| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around a Mixed Variable Type
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
use
DIM h as Variant "Excel Monkey" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
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 "Excel Monkey" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
Target.Row + h is retuning the error; which is something like adding 2+2:
If this post helps click Yes --------------- Jacob Skaria "Excel Monkey" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
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 "Eric G" wrote: 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 "Excel Monkey" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
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 "Patrick Molloy" wrote: use DIM h as Variant "Excel Monkey" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
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 -- Rick (MVP - Excel) "Excel Monkey" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
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 "Excel Monkey" wrote: 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 "Eric G" wrote: 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 "Excel Monkey" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
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 "A3:D12" this would give you "3:12". Is that what you're after? "Excel Monkey" wrote: 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 "Eric G" wrote: 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 "Excel Monkey" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
Thanks Rick,I am going with Eric G's last response.
Thanks EM "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "Excel Monkey" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Getting around a Mixed Variable Type
Excellent point! It was starting to feel a little counter-intuitive.
Thanks EM "Eric G" wrote: 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 "A3:D12" this would give you "3:12". Is that what you're after? "Excel Monkey" wrote: 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 "Eric G" wrote: 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 "Excel Monkey" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What Variable-type should I use | Excel Programming | |||
| Excel 2007 - mixed type (line, column, stacked column) in one char | Charts and Charting in Excel | |||
| DCount, with mixed type data | Excel Discussion (Misc queries) | |||
| Mixed variable problem | Excel Programming | |||
| Problem with copying variable(s) to cell(s) and converting strings to mixed case | Excel Programming | |||
![Excel Monkey[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
