Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-05-2017, 10:21 AM
Radtastic10 Radtastic10 is offline Performing calculations on non adjacent columns Windows 7 64bit Performing calculations on non adjacent columns Office 2010 64bit
Novice
Performing calculations on non adjacent columns
 
Join Date: Jun 2017
Posts: 5
Radtastic10 is on a distinguished road
Default Performing calculations on non adjacent columns


I have to do a calculation on multiple columns that are not next to each other. Is it easier to just copy and paste the current code that I have for the one column for each of the specific columns. Or is there an easier way to accomplish this in my macro?
Reply With Quote
  #2  
Old 06-05-2017, 11:09 AM
charlesdh charlesdh is offline Performing calculations on non adjacent columns Windows 7 32bit Performing calculations on non adjacent columns Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

It would be easier for the forum to see what you are talking about. Can you provide a workbook.
Reply With Quote
  #3  
Old 06-05-2017, 12:11 PM
Radtastic10 Radtastic10 is offline Performing calculations on non adjacent columns Windows 7 64bit Performing calculations on non adjacent columns Office 2010 64bit
Novice
Performing calculations on non adjacent columns
 
Join Date: Jun 2017
Posts: 5
Radtastic10 is on a distinguished road
Default

I can't post the workbooks, but here is the macro that I am working with. This accomplishes it for one column.

What I am asking is would copying and pasting this multiple times (once for each column I need to perform the calculation on) or is there an easier way to accomplish the end goal.

Example of the columns would be: If columns B, K, N and X needed to have the calculation performed on them.


Code:
With Sheet2
    rng = .Range("B" & .Rows.Count).End(xlUp).Row
End With
        
For i = 2 To rng
x = ActiveSheet.Range("B" & i).Value / 3.3
ActiveSheet.Range("B" & i).Value = x
Next i
Worksheets("Sheet2").Columns("B").NumberFormat = "###0.0"
Reply With Quote
  #4  
Old 06-06-2017, 01:33 PM
charlesdh charlesdh is offline Performing calculations on non adjacent columns Windows 7 32bit Performing calculations on non adjacent columns Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

Quote:
If columns B, K, N and X needed to have the calculation performed on them
How do you determine which column needs the calculations?
How many column are you working with.
A redacted sample would help.
Reply With Quote
  #5  
Old 06-07-2017, 05:47 AM
NoSparks NoSparks is offline Performing calculations on non adjacent columns Windows 7 64bit Performing calculations on non adjacent columns Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 807
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

Assuming it's you, and not something that determines what columns to work on programmatically,
put your code inside a loop to use a variable for the column to work on.
Replace the "B"'s you have in your code with that variable, for example...
Code:
For j = 1 To 4
    'which column to loop
    col = Choose(j, "B", "K", "N", "X")

    With Sheet2
        LastRow = .Range(col & .Rows.Count).End(xlUp).Row
        
        For i = 2 To LastRow
            .Range(col & i).Value = .Range(col & i).Value / 3.3
        Next i

        'just format what's needed not entire columns
        .Range(col & "2:" & col & LastRow).NumberFormat = "###0.0"
    End With
Next j
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Performing calculations on non adjacent columns Table bottom border not performing well herpez Word Tables 2 01-16-2017 03:43 PM
How to return after performing a search WaltR Word 2 12-26-2014 05:15 PM
Performing calculations on non adjacent columns Macro not Performing Formula Update as Expected tandchas Excel Programming 7 06-27-2014 05:42 AM
Performing calculations on non adjacent columns Performing filtering to tasks that occur during the next three months jessy Project 1 05-30-2014 05:40 AM
Cumulative calculations for two columns in a table to be placed in the footer noelr Word VBA 0 09-05-2013 04:18 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 03:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft