Storing cell location
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! |
Storing cell location
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" wrote in message oups.com... 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! |
Storing cell location
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" wrote in message ... 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" wrote in message oups.com... 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! |
All times are GMT +1. The time now is 09:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com