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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Joe i did try yours. It poped up the error message about runtime error 1004 method range of object _gobal failed
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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