VBA help with adding 2 columns

Gorandulac32

New Member
Joined
Jan 9, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hi - i am a VBA beginner and I wondering how to add 2 cells (say B1 and C1) and have the results in A1. I want to do this for every sheet in my workbook and for every cell in column B and C.

Essentially B1+C1 in A1, B2+C2 in A2 and so on. I want the formula to drag down to the last row of data for each sheet.

thanks
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Try this:
VBA Code:
Sub MyMacro()

    Dim ws As Worksheet
    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Loop through all sheets
    For Each ws In Worksheets
'       Find last row in column B with data
        lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
'       Populate formulas in column A
        ws.Range("A1:A" & lr).FormulaR1C1 = "=RC[1]+RC[2]"
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Try this:
VBA Code:
Sub MyMacro()

    Dim ws As Worksheet
    Dim lr As Long
   
    Application.ScreenUpdating = False
   
'   Loop through all sheets
    For Each ws In Worksheets
'       Find last row in column B with data
        lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
'       Populate formulas in column A
        ws.Range("A1:A" & lr).FormulaR1C1 = "=RC[1]+RC[2]"
    Next ws
   
    Application.ScreenUpdating = True
   
End Sub
Thank you. Code works great.

what would be the code to start in row 2 since the first row is a header?
 
Upvote 0
Simply change "A1" to "A2" in this formula:
VBA Code:
        ws.Range("A1:A" & lr).FormulaR1C1 = "=RC[1]+RC[2]"
 
Upvote 0
Is there any data in column B past row 1?

If the possibility of no data in column B exists, then use this version:
VBA Code:
Sub MyMacro()

    Dim ws As Worksheet
    Dim lr As Long
    
    Application.ScreenUpdating = False
    
'   Loop through all sheets
    For Each ws In Worksheets
'       Find last row in column B with data
        lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
'       Check to see if last row is after row 1
        If lr > 1 Then
'           Populate formulas in column A
            ws.Range("A2:A" & lr).FormulaR1C1 = "=RC[1]+RC[2]"
        End If
    Next ws
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Solution
Thank you it works.

If I wanted to do the same thing except except add an IF statement to another column say D, would it be the same exact code as above, except that i change ws.Range syntax?

thanks again
 
Upvote 0
Thank you it works.

If I wanted to do the same thing except except add an IF statement to another column say D, would it be the same exact code as above, except that i change ws.Range syntax?

thanks again
You may also need to change the "lr" calculation line.
You need to determine which column you can use to locate the last row with data, and update the column reference in that calculation accordingly.
The comments I added to the code should make it obvious what needs to be changed on that line.
 
Upvote 0
Thank you, I got my macro to run exceptionally well but i have trouble with 3 things.

Essentially my macro pulls files from a drive and then inserts a tab for each file it pulls then inserts 4 columns and fills formulas i need.

1st for the file path it looks up to, it is dynamic meaning each month the file path will be different. How do i account for that in the macro with a message box or something so I don’t need to change the code? C:/Folder/2021/2021-06 is the current path.

Second - I want to delete certain data based on column A criteria which is include/exclude, obviously deleting data that is to be excluded.

Finally the tabs created have a date in form 0601file, 0602 file and so on, is there any way to populate a date column based on the name of the tab?

Thanks
 
Upvote 0
These questions are really entirely different than the original question asked. New questions should really be posted in new threads, so that they appear to be new, unanswered questions (that will show up in the "Unanswered threads" listing that many users use to look for new, unanswered questions).

However, here are some quick replies to your questions.

1st for the file path it looks up to, it is dynamic meaning each month the file path will be different. How do i account for that in the macro with a message box or something so I don’t need to change the code? C:/Folder/2021/2021-06 is the current path.
You could use an Input Box to prompt for it and set it equal to a variable that you can then use in your code, i.e.

VBA Code:
Dim myPath as String
myPath = InputBox("Please enter the file path...")

Second - I want to delete certain data based on column A criteria which is include/exclude, obviously deleting data that is to be excluded.
I would recommend maybe using filters.

Finally the tabs created have a date in form 0601file, 0602 file and so on, is there any way to populate a date column based on the name of the tab?
If all your tab names start with "mmdd...", then you could use this could snippet to convert that as a date ina variable, and then you can use your variable to populate your column, i.e.
VBA Code:
    Dim dte As Date
    dte = DateValue(Left(ActiveSheet.Name, 2) & "/" & Mid(ActiveSheet.Name, 3, 2))
    Range("A1:A100") = dte
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top