Trying to assign a cell values, but keep reminding "type mismatch"

Question

Thanks for answers. I realize where I made mistakes so I modified a little bit but a new error reminder popped up. It keeps reminding me "expected :(" on "Instra = Instrb + 2" row.

Option Explicit

Sub separate()

Dim instrb As Integer
Dim Instra As Integer
Dim i As Integer

i = 2

Do Until Worksheets(1).Cells(i, "A") = ""
instrb = InStr(Cells(i, "A").Text, "pm")
Instra = Instrb + 2
    Cells(i, "B").Value = Right(Worksheets(1).Cells(i, "A"), (Len(Worksheets(1).Cells(i, "A")) - Instra))
    i = i + 1
Loop

End Sub

I am a newbie on coding so I know my work looks stupid. What I am trying to do is copy all the words on right side of "pm" in column A and paste into column B. However it keeps reminding me "type mismatch". I think I am doing right so not sure what is going on.

Sub separate()
    Dim instrb As Integer
    Dim Instra As Integer
    Dim i As Integer

    i = 2

    Do Until Worksheets(1).Cells(i, "A") = ""
        instrb = InStr(Cells(i, "A").Text, "pm")
        Instra = Instrab + 2
        Cells(i, "B").Value = Right(Worksheets(1).Cells(i, "A"), Len(Worksheets(1).Cells(i, "A") - Instra) + 2).Text
        i = i + 1
    Loop
End Sub

What is `Instrab`? Do you have `Option Explicit` at the top of your module? Also, what line is throwing the error? — John Coleman, Jun 24 '19 at 12:52
Since you've declared your `instrb` and `Instra` variables as `Integer` (numeric) you can't assign string (text) values to them. I think that's what is happening here — Tim Stack, Jun 24 '19 at 12:54
@TimStack but `InStr` returns a number ;) Where did the OP try to assign text? I think the issue is here `Worksheets(1).Cells(i, "A") - Instra` and `Worksheets(1).Cells(i, "A")` does not return a number. And the `Right` function has no `.Text` — Pᴇʜ, Jun 24 '19 at 12:56
@Pᴇʜ Totally missed that, must be the heat as it's 30c in my room — Tim Stack, Jun 24 '19 at 13:08

Gary's Student · Answer 1 · 2019-06-24T13:36:35.673

1

Consider:

Sub separate()
    Dim instrbb As Integer
    Dim instra As Integer
    Dim i As Integer

    i = 2

    Do Until Worksheets(1).Cells(i, "A") = ""
        instrbb = InStr(Cells(i, "A").Text, "pm")
        instra = instrbb + 2
        Cells(i, "B").Value = Right(Worksheets(1).Cells(i, "A").Text, (Len(Worksheets(1).Cells(i, "A")) - instra + 1))
        i = i + 1
    Loop
End Sub

But you can simplify with:

Sub separate()
    Dim instrbb As Integer
    Dim instra As Integer
    Dim i As Integer
    Dim s As String

    i = 2
    With Worksheets(1)
        Do Until .Cells(i, "A") = ""
            s = .Cells(i, "A").Text
            .Cells(i, "B").Value = Split(s, "pm")(1)
            i = i + 1
        Loop
    End With
End Sub

NOTES:

avoided using Instrb as a variable as it is a pre-defined VBA function
corrected simple math errors in parsing the string
as others have mentioned, it would be wise to use Long in place of Integer
as others have mentioned, it would be wise to use Option Explicit

edited Jun 24 '19 at 13:36

answered Jun 24 '19 at 13:05

Gary's Student

95,722
10
59
99

1

Consider `i As Long` since it is row counting and Excel has more rows than fit into `Integer`. – Pᴇʜ Jun 24 '19 at 13:08
Your code is great and works perfectly on my computer! However I am still confused why name the variable as instrbb instead of my instrb? It seems like it doesn't work with instrb and keeps reminding "expected :(". Besides I need copy your code to make sure it works. If I type it manually follow your codes, it still doesn't work, reminding me "invalid procedure call or argument" on the longest row even though I typed the exactly same as yours. @Gary's Student – Anctor Hu Jun 24 '19 at 13:15
Actually the [InStr function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function) returns a `Long` too :) – Pᴇʜ Jun 24 '19 at 13:16
Actually I am confused with the "+1" in "Len(Worksheets(1).Cells(i, "A")) - instra + 1". Mind elaborating? @Gary's Student – Anctor Hu Jun 24 '19 at 13:21
2

As a side note, declaring variables as integers on 32-bit systems does not have any advantages over `Long` and [may even hurt performance](https://stackoverflow.com/a/26409520/10540017). – Tim Stack Jun 24 '19 at 13:28

Trying to assign a cell values, but keep reminding "type mismatch"

1 Answers1