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?

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?

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?

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?

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?

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?

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?