1

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.....
Improve this question
1
  • 1
    C'mon ... please don't ask me to "read and understand your entire subroutine!" (I've got plenty of bugs of my own!) ;-) Please edit your question to concentrate it. Reduce it down to the shortest possible representation of your essential question. If you will most-kindly do that, then I'm quite sure that you'll speedily get the answer that you seek. (Another way of saying it: "don't ask us to look for your question!") Commented Jul 26, 2016 at 21:22

1 Answer 1

Reset to default
3

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...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.