Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
Howdy,
Seems so simple, but gives the "run time error 1004". "defined_Range" is a dynamic range in the workbook. Dim myCell as Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell Regards, Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
I have not been able to use this structure myself if the Named Range is
dynamic. It seems that it only works for fixed ranges. "tim" wrote: Howdy, Seems so simple, but gives the "run time error 1004". "defined_Range" is a dynamic range in the workbook. Dim myCell as Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell Regards, Tim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
Tim,
Your formula was incorrect, and you had the wrong variable in the Next clause. This worked fine for me, with a dynamic range Dim myCell As Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500)+1" myCell.Formula = myCell.Value End If Next myCell -- HTH RP (remove nothere from the email address if mailing direct) "tim" wrote in message ... Howdy, Seems so simple, but gives the "run time error 1004". "defined_Range" is a dynamic range in the workbook. Dim myCell as Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell Regards, Tim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
Dynamic range should work fine in this usage as long as it defines a
legitimate range. -- Regards, Tom Ogilvy "gocush" /delete wrote in message ... I have not been able to use this structure myself if the Named Range is dynamic. It seems that it only works for fixed ranges. "tim" wrote: Howdy, Seems so simple, but gives the "run time error 1004". "defined_Range" is a dynamic range in the workbook. Dim myCell as Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell Regards, Tim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
Tim
My quick response was untested. Apparently, the situation I am referring to is similar but not quite the same. I'll have to go back and check to see where the Dynamic range didn't work for me. sorry "tim" wrote: Howdy, Seems so simple, but gives the "run time error 1004". "defined_Range" is a dynamic range in the workbook. Dim myCell as Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell Regards, Tim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
Howdy,
Thanks all for the replies. I thought it should work. I did make some minir edits got it to work, albeit slowly. I have decided to populate a varient array/range instead. Seems to be mucho faster.... Dim vArr As Variant Dim i As Integer Dim myRange As Range Set myRange = Range("defined_range") With myRange ReDim vArr(1 To .Rows.Count, 1 To 1) For i = 1 To UBound(vArr, 1) vArr(i, 1) = myRange(i, 1) 'Get the existing value from the range If IsEmpty(vArr(i, 1)) Then 'If blank, then create random number for location vArr(i, 1) = "=Int(rand()*500+1)" End If Next i .Value = vArr .Formula = .Value 'store the value versus the equation .Sort _ key1:=.Cells(1, 1) 'sort the range ascending .Cells(1, 1).Select End With Not sure it is the most optimized code, but seems to do the job... Regards, Tim "gocush" wrote: Tim My quick response was untested. Apparently, the situation I am referring to is similar but not quite the same. I'll have to go back and check to see where the Dynamic range didn't work for me. sorry "tim" wrote: Howdy, Seems so simple, but gives the "run time error 1004". "defined_Range" is a dynamic range in the workbook. Dim myCell as Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell Regards, Tim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
without looping:
sub AAA() Dim myRange As Range Dim myRange1 as Range Set myRange = Range("defined_range") On Error Resume Next Set myRange1 = myRange.specialCells(xlBlanks) On Error goto 0 if not MyRange1 is Nothing then MyRange1.formula = "=Int(rand()*500+1)" End if With MyRange .Formula = .Value .Sort _ key1:=.Cells(1, 1) 'sort the range ascending .Cells(1, 1).Select End With end Sub -- Regards, Tom Ogilvy "tim" wrote in message ... Howdy, Thanks all for the replies. I thought it should work. I did make some minir edits got it to work, albeit slowly. I have decided to populate a varient array/range instead. Seems to be mucho faster.... Dim vArr As Variant Dim i As Integer Dim myRange As Range Set myRange = Range("defined_range") With myRange ReDim vArr(1 To .Rows.Count, 1 To 1) For i = 1 To UBound(vArr, 1) vArr(i, 1) = myRange(i, 1) 'Get the existing value from the range If IsEmpty(vArr(i, 1)) Then 'If blank, then create random number for location vArr(i, 1) = "=Int(rand()*500+1)" End If Next i .Value = vArr .Formula = .Value 'store the value versus the equation .Sort _ key1:=.Cells(1, 1) 'sort the range ascending .Cells(1, 1).Select End With Not sure it is the most optimized code, but seems to do the job... Regards, Tim "gocush" wrote: Tim My quick response was untested. Apparently, the situation I am referring to is similar but not quite the same. I'll have to go back and check to see where the Dynamic range didn't work for me. sorry "tim" wrote: Howdy, Seems so simple, but gives the "run time error 1004". "defined_Range" is a dynamic range in the workbook. Dim myCell as Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell Regards, Tim |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
"tim" wrote in message
... Howdy, For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell just, as idea, why not use "selection" instead of named range? it is a very quick method of passing area to macro. just select cells you are intertested on worksheet, alt+f8, select macro name, and done! sub test1 Dim c As Variant For Each c In Selection.Cells MsgBox CStr(c.Value) Next end sub you don't need "next [varname]", since interpreter use right variable next only is sufficiant. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
Tom,
Thanks for the alternative. Just to make sure that I understand the following line: if not MyRange1 is Nothing then This statement is checking to see if there are any blank cells, correct? IOW, if there is 'nothing' in MyRange1, then there are no blank cells that need attention. Regards, Tim "Tom Ogilvy" wrote: without looping: sub AAA() Dim myRange As Range Dim myRange1 as Range Set myRange = Range("defined_range") On Error Resume Next Set myRange1 = myRange.specialCells(xlBlanks) On Error goto 0 if not MyRange1 is Nothing then MyRange1.formula = "=Int(rand()*500+1)" End if With MyRange .Formula = .Value .Sort _ key1:=.Cells(1, 1) 'sort the range ascending .Cells(1, 1).Select End With end Sub -- Regards, Tom Ogilvy "tim" wrote in message ... Howdy, Thanks all for the replies. I thought it should work. I did make some minir edits got it to work, albeit slowly. I have decided to populate a varient array/range instead. Seems to be mucho faster.... Dim vArr As Variant Dim i As Integer Dim myRange As Range Set myRange = Range("defined_range") With myRange ReDim vArr(1 To .Rows.Count, 1 To 1) For i = 1 To UBound(vArr, 1) vArr(i, 1) = myRange(i, 1) 'Get the existing value from the range If IsEmpty(vArr(i, 1)) Then 'If blank, then create random number for location vArr(i, 1) = "=Int(rand()*500+1)" End If Next i .Value = vArr .Formula = .Value 'store the value versus the equation .Sort _ key1:=.Cells(1, 1) 'sort the range ascending .Cells(1, 1).Select End With Not sure it is the most optimized code, but seems to do the job... Regards, Tim "gocush" wrote: Tim My quick response was untested. Apparently, the situation I am referring to is similar but not quite the same. I'll have to go back and check to see where the Dynamic range didn't work for me. sorry "tim" wrote: Howdy, Seems so simple, but gives the "run time error 1004". "defined_Range" is a dynamic range in the workbook. Dim myCell as Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell Regards, Tim |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
Sali,
Thanks for the comments... The reason I elected not to do a "selection" is the range is dynamic, depending on other inputs. I could select the range, but that seems to be redundant. My impression is, if you can do something without "selecting" you are better off (again my impression). The reason I use the [varname] with "next" is for sanity, as the loops become nested (beyond this example) it helps me to know which "next" goes where... Regards, Tim "sali" wrote: "tim" wrote in message ... Howdy, For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell just, as idea, why not use "selection" instead of named range? it is a very quick method of passing area to macro. just select cells you are intertested on worksheet, alt+f8, select macro name, and done! sub test1 Dim c As Variant For Each c In Selection.Cells MsgBox CStr(c.Value) Next end sub you don't need "next [varname]", since interpreter use right variable next only is sufficiant. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each cell in Range
That would be correct.
-- Regards, Tom Ogilvy "tim" wrote in message ... Tom, Thanks for the alternative. Just to make sure that I understand the following line: if not MyRange1 is Nothing then This statement is checking to see if there are any blank cells, correct? IOW, if there is 'nothing' in MyRange1, then there are no blank cells that need attention. Regards, Tim "Tom Ogilvy" wrote: without looping: sub AAA() Dim myRange As Range Dim myRange1 as Range Set myRange = Range("defined_range") On Error Resume Next Set myRange1 = myRange.specialCells(xlBlanks) On Error goto 0 if not MyRange1 is Nothing then MyRange1.formula = "=Int(rand()*500+1)" End if With MyRange .Formula = .Value .Sort _ key1:=.Cells(1, 1) 'sort the range ascending .Cells(1, 1).Select End With end Sub -- Regards, Tom Ogilvy "tim" wrote in message ... Howdy, Thanks all for the replies. I thought it should work. I did make some minir edits got it to work, albeit slowly. I have decided to populate a varient array/range instead. Seems to be mucho faster.... Dim vArr As Variant Dim i As Integer Dim myRange As Range Set myRange = Range("defined_range") With myRange ReDim vArr(1 To .Rows.Count, 1 To 1) For i = 1 To UBound(vArr, 1) vArr(i, 1) = myRange(i, 1) 'Get the existing value from the range If IsEmpty(vArr(i, 1)) Then 'If blank, then create random number for location vArr(i, 1) = "=Int(rand()*500+1)" End If Next i .Value = vArr .Formula = .Value 'store the value versus the equation .Sort _ key1:=.Cells(1, 1) 'sort the range ascending .Cells(1, 1).Select End With Not sure it is the most optimized code, but seems to do the job... Regards, Tim "gocush" wrote: Tim My quick response was untested. Apparently, the situation I am referring to is similar but not quite the same. I'll have to go back and check to see where the Dynamic range didn't work for me. sorry "tim" wrote: Howdy, Seems so simple, but gives the "run time error 1004". "defined_Range" is a dynamic range in the workbook. Dim myCell as Range For Each myCell In Range("Defined_Range") If IsEmpty(myCell) Then myCell.Formula = "=Int(rand()*500+1" myCell.Formula = myCell.Value End If Next rCell Regards, Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming |