Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Little More Advanced Range Fill-In
I have this function:
myKey = Key() Selection.Value = myKey If the user highlights a range, a specific value is filled into every single cell highlighted. I want to do this a little better. If the user highlights several rows and columns, I want the myKey to only fill in if the column header (Row 1 value) is "Key" - is this possible in VBA? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Little More Advanced Range Fill-In
Sub test()
myKey = Key() For Each cell In ActiveSheet.Selection If Cells(1, cell.Column) = "Key" Then cell.Value = myKey End If Next cell End Sub "Derek Hart" wrote: I have this function: myKey = Key() Selection.Value = myKey If the user highlights a range, a specific value is filled into every single cell highlighted. I want to do this a little better. If the user highlights several rows and columns, I want the myKey to only fill in if the column header (Row 1 value) is "Key" - is this possible in VBA? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Little More Advanced Range Fill-In
I get the error:
"Compile Error: For Each control variable must be Variant or Object" How would I correct this code? Derek "Joel" wrote in message ... Sub test() myKey = Key() For Each cell In ActiveSheet.Selection If Cells(1, cell.Column) = "Key" Then cell.Value = myKey End If Next cell End Sub "Derek Hart" wrote: I have this function: myKey = Key() Selection.Value = myKey If the user highlights a range, a specific value is filled into every single cell highlighted. I want to do this a little better. If the user highlights several rows and columns, I want the myKey to only fill in if the column header (Row 1 value) is "Key" - is this possible in VBA? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Little More Advanced Range Fill-In
did you have the error before my changes? I don't get the error with Excel
2003. Can you post your entire code? "Derek Hart" wrote: I get the error: "Compile Error: For Each control variable must be Variant or Object" How would I correct this code? Derek "Joel" wrote in message ... Sub test() myKey = Key() For Each cell In ActiveSheet.Selection If Cells(1, cell.Column) = "Key" Then cell.Value = myKey End If Next cell End Sub "Derek Hart" wrote: I have this function: myKey = Key() Selection.Value = myKey If the user highlights a range, a specific value is filled into every single cell highlighted. I want to do this a little better. If the user highlights several rows and columns, I want the myKey to only fill in if the column header (Row 1 value) is "Key" - is this possible in VBA? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Little More Advanced Range Fill-In
Dim myKey As String
myKey = Key() For Each objCell In ActiveSheet.Selection If Cells(1, objCell.Column) = "Key" Then objCell.Value = myKey End If Next objCell What do you think? "Joel" wrote in message ... did you have the error before my changes? I don't get the error with Excel 2003. Can you post your entire code? "Derek Hart" wrote: I get the error: "Compile Error: For Each control variable must be Variant or Object" How would I correct this code? Derek "Joel" wrote in message ... Sub test() myKey = Key() For Each cell In ActiveSheet.Selection If Cells(1, cell.Column) = "Key" Then cell.Value = myKey End If Next cell End Sub "Derek Hart" wrote: I have this function: myKey = Key() Selection.Value = myKey If the user highlights a range, a specific value is filled into every single cell highlighted. I want to do this a little better. If the user highlights several rows and columns, I want the myKey to only fill in if the column header (Row 1 value) is "Key" - is this possible in VBA? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Little More Advanced Range Fill-In
chaging the variable cell to objcell doesn't solve your problem. there is
something else wrong. I need to see the entire code. "Derek Hart" wrote: Dim myKey As String myKey = Key() For Each objCell In ActiveSheet.Selection If Cells(1, objCell.Column) = "Key" Then objCell.Value = myKey End If Next objCell What do you think? "Joel" wrote in message ... did you have the error before my changes? I don't get the error with Excel 2003. Can you post your entire code? "Derek Hart" wrote: I get the error: "Compile Error: For Each control variable must be Variant or Object" How would I correct this code? Derek "Joel" wrote in message ... Sub test() myKey = Key() For Each cell In ActiveSheet.Selection If Cells(1, cell.Column) = "Key" Then cell.Value = myKey End If Next cell End Sub "Derek Hart" wrote: I have this function: myKey = Key() Selection.Value = myKey If the user highlights a range, a specific value is filled into every single cell highlighted. I want to do this a little better. If the user highlights several rows and columns, I want the myKey to only fill in if the column header (Row 1 value) is "Key" - is this possible in VBA? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Little More Advanced Range Fill-In
I switched ActiveSheet.Selection to just Selection and it solved it. Not
sure why the ActiveSheet reference does not work for me, but it works. Do you know why I cannot reference ActiveSheet? "Joel" wrote in message ... chaging the variable cell to objcell doesn't solve your problem. there is something else wrong. I need to see the entire code. "Derek Hart" wrote: Dim myKey As String myKey = Key() For Each objCell In ActiveSheet.Selection If Cells(1, objCell.Column) = "Key" Then objCell.Value = myKey End If Next objCell What do you think? "Joel" wrote in message ... did you have the error before my changes? I don't get the error with Excel 2003. Can you post your entire code? "Derek Hart" wrote: I get the error: "Compile Error: For Each control variable must be Variant or Object" How would I correct this code? Derek "Joel" wrote in message ... Sub test() myKey = Key() For Each cell In ActiveSheet.Selection If Cells(1, cell.Column) = "Key" Then cell.Value = myKey End If Next cell End Sub "Derek Hart" wrote: I have this function: myKey = Key() Selection.Value = myKey If the user highlights a range, a specific value is filled into every single cell highlighted. I want to do this a little better. If the user highlights several rows and columns, I want the myKey to only fill in if the column header (Row 1 value) is "Key" - is this possible in VBA? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced excel fill handle Question | Excel Discussion (Misc queries) | |||
Excel fill handle use -advanced | Excel Discussion (Misc queries) | |||
advanced filter a range | Excel Worksheet Functions | |||
Advanced Filter Range Selection in VB | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |