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!!!!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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
 

cfluegel

New Member
Joined
Nov 22, 2011
Messages
14
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!!!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Is Blue Shield POS a named range or just a value in a cell?
 

cfluegel

New Member
Joined
Nov 22, 2011
Messages
14

ADVERTISEMENT

That's the value in the cell
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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 ...
 

cfluegel

New Member
Joined
Nov 22, 2011
Messages
14
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!!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

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