VBA reference cell name and select corresponding cell

cfluegel

New Member
Joined
Nov 22, 2011
Messages
14
Hi All, I'm VERY new to VBA. I can write formulas in excel plenty but VBA is quite different.

I have a workbook that has volume changes in data from month to month. What I want to do is write a Macro to reference a cell name(ex. "Plan ABC) and then kick off the following code in the cell offset(5, 10)

Sub ERRP_BenBillingDetail()
'
' ERRP_BenBillingDetail Macro
' ERRP Credit update for the Benefit Billing Detail Tab
'
'
Do
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=""Employee Only"",RC[-1]+9.43,IF(OR(RC[-2]=""Employee and One Dependent"",RC[-2]=""Employee and Spouse or Domestic Partner""),RC[-1]+18.86,IF(RC[-2]=""Family"",RC[-1]+26.69,"""")))"

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

End Sub

How do I write a code to reference the name of a cell and start the formula above in another cell?

Thanks in advance for any help!!!!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Maybe ...

Code:
Sub ERRP_BenBillingDetail()
    Dim r           As Range

    Set r = Range("Plan ABC").Offset(5, 10)
    Set r = Range(r, r.End(xlDown))

    r.FormulaR1C1 = "=IF(RC[-2]=""Employee Only"", RC[-1] + 9.43, " & _
                    "IF(OR(RC[-2]=""Employee and One Dependent"", RC[-2]=""Employee and Spouse or Domestic Partner""),RC[-1] + 18.86, " & _
                    "IF(RC[-2]=""Family"", RC[-1] + 26.69, """")))"
End Sub
 
Upvote 0
Thanks for the reply shg! Any ideas what I'm not doing right here? I'm getting the following message when using that code.

Run-time error '1004':
Method 'Range' of object'_Global' failed

Only change was using the actual name of the plan in place of ("Plan ABC")
Set r = Range("Blue Shield POS").Offset(5, 10)


I have been experimenting with:
Cells.Find(What:="Blue Shield POS", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(5, 10) = ActiveCell
But haven't been able to get that to select the proper cell to kick off the formula.

Thanks again for your help!!!
 
Upvote 0
Is Blue Shield POS a named range or just a value in a cell?
 
Upvote 0
Then you need to use Find:

Code:
    Dim rFind       As Range

    Set rFind = Cells.Find(What:="Blue Shield POS", _
                          LookIn:=xlValues, _
                          LookAt:=xlPart)
    If rFind Is Nothing Then
        MsgBox "Not found"
        Exit Sub
    End If

    ' do whatever ...
 
Upvote 0
Thanks Again shg! I appreciate the effort you put in to help me out!!

I ended up going with:

Cells.Find(What:="Blue Shield POS", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(4, 9).Select

Do
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=""Employee Only"",RC[-1]+9.43,IF(OR(RC[-2]=""Employee and One Dependent"",RC[-2]=""Employee and Spouse or Domestic Partner""),RC[-1]+18.86,IF(RC[-2]=""Family"",RC[-1]+26.69,"""")))"

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

Thanks for pointing me in the right direction!!
 
Upvote 0
Good job.

The first line will generate a run-time error if the value is not found -- that's the reason to set a range variable, and then test it whenever you use Find.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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