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: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
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



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

All times are GMT -7. The time now is 05:35 PM.


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