"Anti" Trim in VBA - I want to keep the white space?

[dircur]

Hi and thanks in advance if you've got the mojo.

I am copying the value of the active cell to a named range cell but I am having an interesting problem. My code is as follows.

V_Tempvalue = ActiveCell.Value
Range("NameHere").Value = V_Tempvalue

The values in the active cell can vary greatly and may include leading spaces. When the ActiveCell contains leading spaces (presumably trailing as well) and only numbers excel imediately converts the value to a number value stripping the spaces. (Most times this would make me happy, but today I need those spaces.)
I could build a function and use len left and right to determin leading and trailing spaces but I figure there has to be a simpler way to load the variable to include the spaces that I am just not thinking of. Is there a better property than "Value".

Thanks,

I have braced myself to feel stupid when someone passes the brain freeze I am having.

 

[Norie]

Try using Text instead of Value, ie ActiveCell.Text.

You might also need to declare V_TempValue as a string if you haven't already.

 

[JackDanIce]

Try running this code and see if it works as what you want or if you can then modify it to what you want:

Sub triangles()
Dim testme As String
Range("A5") = "   testing   "
Range("A5").Select
testme = ActiveCell.Text
Range("A1") = testme
End Sub

 

[dircur]

Thanks for your time.

.Text appears to return a trimed text as well. Even when the variable is declared as String.


Norie you nailed me for not declaring the variable, I hate option explicite and don't normally build long code. I may not be a programmer but it's lazy and often solves these sort of problems.
thanks.

I have further noticed some awesome irony in this. I test the value against a listing of values I didn't question why that wasn't working but because I use the .value in the comparison it finds what it is looking for. So the variable assignment passes the test only to later fail when used in a cell lookup using trimmed text. Interestingly I added a msgbox with the len of the Activecell.Text and it returns the full length just not the full string. Using that I will pad the variable as it is assigned.

Here's the full Sub minus the padding I'll repost once I am done.

Oh yeah, I also dont comment my code .

Sub SelectVnd(control As IRibbonControl)
 Dim V_AttemptedVendor As String
 V_AttemptedVendor = ActiveCell.Text
 V_Found = "N"
 For Each Cell In Range("VendorList")
    If Cell.Value = V_AttemptedVendor Then
       V_Found = "Y"
       Exit For
    End If
 Next
 If V_Found = "Y" Then
    Range("VndrSelection") = V_AttemptedVendor
    Sheets("Vendor Report").Select
 Else
    MsgBox ("It appears the current cell (" & V_AttemptedVendor & ")you have selected does not contain a valid Vendor. " & _
           "Please confirm you have an appropriate cell selected. If this Msgbox has been presented " & _
           "in error please copy the value manually to the Vendor report Sheet and attempt it there.")
 End If
End Sub

 

[dircur]

After padding I found the true culprit. There is also leading single quote '. If anything I would have expected excel to leverage that and recognize the full text, but it would appear excell is ignoring it. The data is being imported from another program so I don't have control to remove it before I recieve. I am revamping the code to use Cut and paste special. It works so i'll use it. I'll stuff it in the [If V_found = "Y"]. and repost.

 

[dircur]

Paste special worked. Ironically if I recorded a macro instead of straight coding I may not have had any issue.

Sub SelectVnd(control As IRibbonControl)
 Dim V_AttemptedVendor As String
 Dim V_Found As String
 
 V_AttemptedVendor = ActiveCell.Text
 V_Found = "N"
 For Each Cell In Range("VendorList")
    If Cell.Value = V_AttemptedVendor Then
       V_Found = "Y"
       Exit For
    End If
 Next
 If V_Found = "Y" Then
    ActiveCell.Copy
    Sheets("Vendor Report").Select
    Range("VndrSelection").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 Else
    MsgBox ("It appears the current cell (" & V_AttemptedVendor & ")you have selected does not contain a valid Vendor. " & _
           "Please confirm you have an appropriate cell selected. If this Msgbox has been presented " & _
           "in error please copy the value manually to the Vendor report Sheet and attempt it there.")
 End If
End Sub

 

[Rasm]

Add the character ' in front of the text string.