Trying to add 3 cells together with VBA

[leopilot]

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

 

[sericom]

Does this help

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

 

[leopilot]

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]

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]

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:

    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]

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]

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]

Joe i did try yours. It poped up the error message about runtime error 1004 method range of object _gobal failed

 

[leopilot]

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]

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