Performing calculations on non adjacent columns

Radtastic10 · #1 06-05-2017, 10:21 AM

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?

charlesdh · #2 06-05-2017, 11:09 AM

Hi,

It would be easier for the forum to see what you are talking about. Can you provide a workbook.

Radtastic10 · #3 06-05-2017, 12:11 PM

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"

charlesdh · #4 06-06-2017, 01:33 PM

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.

NoSparks · #5 06-07-2017, 05:47 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
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
Macro not Performing Formula Update as Expected	tandchas	Excel Programming	7	06-27-2014 05:42 AM
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