Getting around a Mixed Variable Type

[Excel Monkey]

I have a loop. Within this loop I have a variable "h" which is appended by
the current version of itself. I am using a delimiter of ":" during each
append. However I keep getting a type mismatch error as I have dimensioned
"h" as a Double. I think adding the ":" triggers the error. I can't change
"h" to a String as the addition logic that creates the appending will not
work. How do I get around this?

Dim h as double

For g = 0 To Target.Rows.Count - 1
If Target.Rows.Count - 1 = 0 Then
h = Target.Row
Else
h = h & Target.Row + h & ":"
End If
Next

Thanks

EM

 

[Patrick Molloy]

use

DIM h as Variant

 

[Eric G]

How about something like below. Convert the row number to a string before
you do the appending. Use whatever numeric format you like.

Dim h As String

For g = 0 To Target.Rows.Count - 1
If Target.Rows.Count - 1 = 0 Then
h = Format(Target.Row, "0")
Else
h = h & format(Target.Row, "0") + h & ":"
End If
Next

HTH,

Eric

 

[Jacob Skaria]

Target.Row + h is retuning the error; which is something like adding 2+2:


If this post helps click Yes

 

[Excel Monkey]

That does not work as I need to add the h:
h = h & Target.Row + h & ":"

Your version had:
h = h & format(Target.Row, "0") + h & ":"

If the target has two rows (7 and 8), I want to get 7:8. Your version
creates "7:77::".

Keeping my original code and changing the variable type to variant fails as
well as it passes the correct values to h on the first pass ("7"). But it
fails on Target.Row + h on the second pass/


Thanks

EM

 

[Patrick Molloy]

sorry

DIM h as STRING

then

For g = 0 To Target.Rows.Count - 1
If Target.Rows.Count = 1 Then ' CHANGED
h = Target.Row
Else
h = h & Target.Row & ":" 'CHANGED
End If
Next

 

[Rick Rothstein]

It is hard to figure out exactly what you are trying to do with the code you
posted. You are assigning the target row to 'h' and then concatenating 'h'
with the target row (which is already assigned to 'h', so why not just use
'h' instead of Target.Row?)... and then you are adding 'h' to something, but
it is not clear whether you want to add it to the target row first, before
concatenating it onto 'h', or if you want to add it to the number produced
by concatenating 'h' and Target.Row (which is what 'h' equals). Then you are
concatenating a colon onto the end of that... it looks like you may be
trying to form a range address, but that is not entirely clear. Can you
clarify what you are actually attempting to do with your code?

Also, most people would write this line...

If Target.Rows.Count - 1 = 0 Then

like this...

If Target.Rows.Count = 1 Then

 

[Excel Monkey]

Sorry there was an error in my original post

h = h & Target.Row + h & ":"

Should have said:

h = h & Target.Row + g & ":"

However this does not change the issue as Target.Row needs to increment but
has a ":" appended to it which causes the error.


Thanks

EM

 

[Eric G]

Yes, I messed that up, but I'm also unclear as to what you're really trying
to do here...

If you are simply trying to get the range of rows in "Target", you can do
something like this:

h = Target.Row & ":" & Target.Row + Target.Rows.Count - 1

You don't need the loop. If target is "A312" this would give you "3:12".

Is that what you're after?

 

[Excel Monkey]

Thanks Rick,I am going with Eric G's last response.

Thanks

EM

 

[Excel Monkey]

Excellent point! It was starting to feel a little counter-intuitive.

Thanks

EM