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

"NacellesEng" <[email protected]> wrote in message
news:[email protected]...
> 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]

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

"Doug Glancy" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> "NacellesEng" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
>
>