Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to define a range using variables
Try this syntax
Sub atlanticcable() Dim myrange As Range x = "A" y = "B" Set myrange = Range(x & "1:" & y & "1") myrange.Select End Sub Mike "Givnik" wrote: How do I assign a range dynamically ie using a string variable, basically I have a sheet that varies in the number of rows and I do a for next loop but as yet have not worked out the syntax for how to use a variable in the Range command... Range("x2:x10").Cells Code so far For Each cell In Range("x2:x10").Cells If cell.PrefixCharacter = "'" Then cell.Value = cell.Value End If Next cell is it posisble to have something like: strRange = "X2:X189" For Each cell In Range(strRange).Cells |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to define a range using variables
Hello Mike, thanks for the quick response, I tried your suggestion and it
will work but I need to have the number of rows as a variable, I have tried a number of combinations of your code but without success.... any other ideas would be welcome NoRows is a integer var holding number of rows my last attempt was as follows x = "W" Set myRange = Range(x & "2:" & x & """ & NoRows & """) For Each cell In myRange.Cells If cell.PrefixCharacter = "'" Then cell.Value = cell.Value End If Next cell "Mike H" wrote: Try this syntax Sub atlanticcable() Dim myrange As Range x = "A" y = "B" Set myrange = Range(x & "1:" & y & "1") myrange.Select End Sub Mike "Givnik" wrote: How do I assign a range dynamically ie using a string variable, basically I have a sheet that varies in the number of rows and I do a for next loop but as yet have not worked out the syntax for how to use a variable in the Range command... Range("x2:x10").Cells Code so far For Each cell In Range("x2:x10").Cells If cell.PrefixCharacter = "'" Then cell.Value = cell.Value End If Next cell is it posisble to have something like: strRange = "X2:X189" For Each cell In Range(strRange).Cells |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to define a range using variables
Mike, Have worked out the syntax... this seems to work
Dim myRange As Range Set myRange = Range("W1:W" & NoRows).Cells For Each cell In myRange If cell.PrefixCharacter = "'" Then cell.Value = cell.Value End If Next cell "Givnik" wrote: Hello Mike, thanks for the quick response, I tried your suggestion and it will work but I need to have the number of rows as a variable, I have tried a number of combinations of your code but without success.... any other ideas would be welcome NoRows is a integer var holding number of rows my last attempt was as follows x = "W" Set myRange = Range(x & "2:" & x & """ & NoRows & """) For Each cell In myRange.Cells If cell.PrefixCharacter = "'" Then cell.Value = cell.Value End If Next cell "Mike H" wrote: Try this syntax Sub atlanticcable() Dim myrange As Range x = "A" y = "B" Set myrange = Range(x & "1:" & y & "1") myrange.Select End Sub Mike "Givnik" wrote: How do I assign a range dynamically ie using a string variable, basically I have a sheet that varies in the number of rows and I do a for next loop but as yet have not worked out the syntax for how to use a variable in the Range command... Range("x2:x10").Cells Code so far For Each cell In Range("x2:x10").Cells If cell.PrefixCharacter = "'" Then cell.Value = cell.Value End If Next cell is it posisble to have something like: strRange = "X2:X189" For Each cell In Range(strRange).Cells |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using variables to define a range of cells? | Excel Programming | |||
VERY Basic Define Variables Help Needed | Excel Programming | |||
How to use variables to define range | Excel Programming | |||
How do you define variables in excel? | Excel Discussion (Misc queries) | |||
Could we define series variables in one statement? | Excel Programming |