Trying to add 3 cells together with VBA

leopilot

New Member
Joined
Dec 24, 2014
Messages
12
So i have some cells that move around, but i can find them by referenceing something else. So i want to add these cells together. mnow here is my code. However i can not get excel to calculate it until i click on it. Any help?


Dim MS As String
Dim MSC As String
Dim MF As String
Dim ADD As String
With ActiveSheet.Range("A1:B500")
Set c = .Find("SAL MED SERV Total", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, -1).Activate

Selection.Offset(0, 17).Select

MS = ActiveCell.Address

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
With ActiveSheet.Range("A1:B500")
Set c = .Find("SAL MEDICAL ADMIN Total", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, -1).Activate


Selection.Offset(0, 17).Select

MSC = ActiveCell.Address


Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

With ActiveSheet.Range("A1:B500")
Set c = .Find("SAL MED FACILITIES Total", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, -1).Activate


Selection.Offset(0, 17).Select

MF = ActiveCell.Address

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With


With ActiveSheet.Range("A1:B500")
Set c = .Find("Grand Total", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, 17).Activate


ActiveCell.Formula = MS & "+" & MF & "+" & MSC



Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
915
Does this help

Code:
ActiveCell.Formula = "=" & MS & "+" & MF & "+" & MSC
 

leopilot

New Member
Joined
Dec 24, 2014
Messages
12
Yea I tried that, but I get a run time error. I tried dim as interger, how got a overload error. I figured this would have been easy :(. This is the last piece of my macro.
 

AkaTrouble

Well-known Member
Joined
Dec 17, 2014
Messages
1,544
i may be wrong (often am) but as it is in a formula string you might need to create text string before placing it as a formula in the cell

for example

Dim MyNewFormula as string

MyNewFormula = "=" & MS & "+" & MF & "+" & MSC

then your cell fill =

ActiveCell.Formula = MyNewFormula
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Both suggestions work for me.

Try temporarily replacing your formula population with this Message Box and tell us what it returns. This tells us the value of each of your variables:
Code:
    MsgBox "MS Address: " & MS & "; MS Value: " & Range(MS) & vbCrLf & _
           "MF Address: " & MF & "; MF Value: " & Range(MF) & vbCrLf & _
           "MSC Address: " & MSC & "; MSC Value: " & Range(MSC)
 

leopilot

New Member
Joined
Dec 24, 2014
Messages
12
So i tried both and non work :( and i tried getting what the address is, and i got another runtime error 1004 method range of object _gobal failed.


However when you hold the cursor over MF or the other ones, they are idtenified with the address.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Did you try the code I provided?
I suspect you have an issue where one might not be returning what you expect (maybe text or an error). The message box should make it evident, if that is the issue.
 

leopilot

New Member
Joined
Dec 24, 2014
Messages
12
Joe i did try yours. It poped up the error message about runtime error 1004 method range of object _gobal failed
 

leopilot

New Member
Joined
Dec 24, 2014
Messages
12
Figured it out... MSC MF and MS are not always there. Sometimes its 1 sometimes its 2 and sometimes all three 3. Well figured out why it was not working. Now i need to get it to work work :)
 

leopilot

New Member
Joined
Dec 24, 2014
Messages
12
OK so i got it to work by setting else MS =0 and so on... But is there are way to take out the $R$13... (take out the dollar signs $) so i can do a copy the formula
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

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
Top