Storing cell location

[NacellesEng]

Okay, heres one I just can't seem to think out. I have a cell that
contains the following:
=$D$245+$D$600

I want to be able to store each cell to a variable. Is there anyway
for me to split and store the cells? The cell numbers change
frequently and can be as small as a single digit or as big as 4 digits.
Thanks!

 

[Doug Glancy]

Here's what I came up with:

Sub test()

Dim cell_formula As String, first_address As String, second_address As
String
Dim plus_location As Long
Dim first_cell As Range, second_cell As Range

With Sheet1.Range("A1")
cell_formula = CStr(.Formula)
plus_location = InStr(2, cell_formula, "+")
first_address = Mid$(cell_formula, 2, plus_location - 2)
second_address = Mid$(cell_formula, plus_location + 1)
Set first_cell = ActiveSheet.Range(first_address)
Set second_cell = ActiveSheet.Range(second_address)
End With

End Sub

hth,

Doug

 

[Doug Glancy]

I went in two directions concerning which sheet you were working with. This
assumes the formula is in range A1 and deals with the ActiveSheet
throughout:

Sub test()

Dim cell_formula As String, first_address As String, second_address As
String
Dim plus_location As Long
Dim first_cell As Range, second_cell As Range

With ActiveSheet
cell_formula = CStr(.Range("A1").Formula)
plus_location = InStr(2, cell_formula, "+")
first_address = Mid$(cell_formula, 2, plus_location - 2)
second_address = Mid$(cell_formula, plus_location + 1)
Set first_cell = .Range(first_address)
Set second_cell = .Range(second_address)
first_cell.Select
End With

End Sub