CODEWITHSUNDEEP
vbatypeconverter
user6576927
user6576927

Reputation:

Converting Integer to String in VBA

Here are a few bits of an overall code, and I am trying to run the variable P1 through an If statement. At the end of the If statement if value P1 does not meet the prior criteria, I would then like to convert P1 to an empty string.

I have deleted most of the irrelevant portions of the code below just to get to the point of things...

Sub SLNR
    '<Macro = SLNR>
    '<Shortcut = Shift+Ctrl+S>
    '<Desc = recorded 7/26/2016 12:33:03 PM>
    On Error Resume Next

    ' Individual lines for the data
    Dim L1 As String, L2 As String, L3 As String, L4 As String, L5 As String, L6 As String, L7 As String, _
        L8 As String, L9 As String, L10 As String, L11 As String, L12 As String, L13 As String, _
        L14 As String, L15 As String, L16 As String, L17 As String, L18 As String, L19 As String, _
        L20 As String

    Dim L As Integer    'Left column of data
    Dim R As Integer    'Right column of data
    Dim R2 As Integer   'The data row to be checked
    Dim pL As Integer
    Dim pR As Integer

    Dim SP1 As String
        SP1 = "subj_data_field1"
    Dim SP2 As String
        SP2 = "subj_data_field2"
    Dim SP3 As String
        SP3 = "subj_data_field3"

    Dim P1 As Integer
    Dim P2 As Integer
    Dim P3 As Integer

    Dim E As String      'Carriage Return Key shortcut
        E = Chr$(13)

    L   =   0
    R   =   10
    R2  =   2

    pL = L + 14
    pR = R + 13

    With ActiveSession
        .InputMode = 1
        .Output "MS1" & E
        .WaitFor 0, 1, "W,/) "


    ' Copying the first few characters of each line in LN report, to compare with the phone fields...
    .Copy   L,  R2  +   0   ,   R,  R2  +   0
        L1 = Clipboard
    .Copy   L,  R2  +   1   ,   R,  R2  +   1
        L2 = Clipboard
    .Copy   L,  R2  +   2   ,   R,  R2  +   2
        L3 = Clipboard
    .Copy   L,  R2  +   3   ,   R,  R2  +   3
        L4 = Clipboard
    .Copy   L,  R2  +   4   ,   R,  R2  +   4
        L5 = Clipboard
    .Copy   L,  R2  +   5   ,   R,  R2  +   5
        L6 = Clipboard
    .Copy   L,  R2  +   6   ,   R,  R2  +   6
        L7 = Clipboard
    .Copy   L,  R2  +   7   ,   R,  R2  +   7
        L8 = Clipboard
    .Copy   L,  R2  +   8   ,   R,  R2  +   8
        L9 = Clipboard
    .Copy   L,  R2  +   9   ,   R,  R2  +   9
        L10 = Clipboard
    .Copy   L,  R2  +   10  ,   R,  R2  +   10
        L11 = Clipboard
    .Copy   L,  R2  +   11  ,   R,  R2  +   11
        L12 = Clipboard
    .Copy   L,  R2  +   12  ,   R,  R2  +   12
        L13 = Clipboard
    .Copy   L,  R2  +   13  ,   R,  R2  +   13
        L14 = Clipboard
    .Copy   L,  R2  +   14  ,   R,  R2  +   14
        L15 = Clipboard
    .Copy   L,  R2  +   15  ,   R,  R2  +   15
        L16 = Clipboard
    .Copy   L,  R2  +   16  ,   R,  R2  +   16
        L17 = Clipboard
    .Copy   L,  R2  +   17  ,   R,  R2  +   17
        L18 = Clipboard
    .Copy   L,  R2  +   18  ,   R,  R2  +   18
        L19 = Clipboard
    .Copy   L,  R2  +   19  ,   R,  R2  +   19
        L20 = Clipboard


    ' Finding field for P1
    If L1 = SP1 Then
        .Copy   pL,     R2  +   0   ,   pR,     R2  +   0
            P1 = Clipboard
    ElseIf L2 = SP1 Then
        .Copy   pL,     R2  +   1   ,   pR,     R2  +   1
            P1 = Clipboard
    ElseIf L3 = SP1 Then
        .Copy   pL,     R2  +   2   ,   pR,     R2  +   2
            P1 = Clipboard
    ElseIf L4 = SP1 Then
        .Copy   pL,     R2  +   3   ,   pR,     R2  +   3
            P1 = Clipboard
    ElseIf L5 = SP1 Then
        .Copy   pL,     R2  +   4   ,   pR,     R2  +   4
            P1 = Clipboard
    ElseIf L6 = SP1 Then
        .Copy   pL,     R2  +   5   ,   pR,     R2  +   5
            P1 = Clipboard
    ElseIf L7 = SP1 Then
        .Copy   pL,     R2  +   6   ,   pR,     R2  +   6
            P1 = Clipboard
    ElseIf L8 = SP1 Then
        .Copy   pL,     R2  +   7   ,   pR,     R2  +   7
            P1 = Clipboard
    ElseIf L9 = SP1 Then
        .Copy   pL,     R2  +   8   ,   pR,     R2  +   8
            P1 = Clipboard
    ElseIf L10 = SP1 Then
        .Copy   pL,     R2  +   9   ,   pR,     R2  +   9
            P1 = Clipboard
    ElseIf L11 = SP1 Then
        .Copy   pL,     R2  +   10  ,   pR,     R2  +   10
            P1 = Clipboard
    ElseIf L12 = SP1 Then
        .Copy   pL,     R2  +   11  ,   pR,     R2  +   11
            P1 = Clipboard
    ElseIf L13 = SP1 Then
        .Copy   pL,     R2  +   12  ,   pR,     R2  +   12
            P1 = Clipboard
    ElseIf L14 = SP1 Then
        .Copy   pL,     R2  +   13  ,   pR,     R2  +   13
            P1 = Clipboard
    ElseIf L15 = SP1 Then
        .Copy   pL,     R2  +   14  ,   pR,     R2  +   14
            P1 = Clipboard
    ElseIf L16 = SP1 Then
        .Copy   pL,     R2  +   15  ,   pR,     R2  +   15
            P1 = Clipboard
    ElseIf L17 = SP1 Then
        .Copy   pL,     R2  +   16  ,   pR,     R2  +   16
            P1 = Clipboard
    ElseIf L18 = SP1 Then
        .Copy   pL,     R2  +   17  ,   pR,     R2  +   17
            P1 = Clipboard
    ElseIf L19 = SP1 Then
        .Copy   pL,     R2  +   18  ,   pR,     R2  +   18
            P1 = Clipboard
    ElseIf L20 = SP1 Then
        .Copy   pL,     R2  +   19  ,   pR,     R2  +   19
            P1 = Clipboard
    Else
        CStr(P1)        
        P1 = ""              ' <<<<<<<<********** ISSUE IS HERE
    End If


    ' ......... Code has been trimmed... Showing below the reason I need P1 as an Integer

    If P1 <> "" Then
        Format(P1,000-000-0000)
    End If
    If P2 <> "" Then
        Format(P2,000-000-0000)
    End If
    If P3 <> "" Then
        Format(P3,000-000-0000)
    End If

' CONTINUES ON.....

Upvotes: 1

Views: 13992

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

You've declared P1 as an Integer - If you want to change it's data type half way through the operation then you should declare it as a Variant instead and use implicit conversion to control the data type during execution although it would make more sense to simply declare another variable as a string and use that instead.

Also, CStr(P1) on it's own doesn't do anything - you need to assign the return value back to the variable:

P1 = CStr(P1)

What would make more sense:

Dim P1 As Integer
Dim P1toString As String

'// More code here

P1toString = CStr(P1)

'// More code here

If P1toString <> "" Then
'// etc etc...

Upvotes: 3

Related Questions