Error reading strings from cells

Question

Sub nationalityscript()
Dim i As Integer
Dim temp As Integer
Dim Country As String
Dim strEnd As Integer
Dim strComplete As String
i = 0
Do While i < 300
    Dim k As Integer
    Dim a As String

    strComplete = Cells(h, i).Value
    strEnd = Len(strComplete)
    k = 0
    Do While k < strEnd
        a = Cells(h, i).Value.Substring(k, k)
        If a = "," Then
            temp = a
        End If
        k = k + 1
    Loop
    Country = Cells(h, i).Value.Substring(temp, strEnd)
i = i + 1
Loop
End Sub

Hi, This is my first day programming in vba so please go easy on me.

I am writing a macro in excel to focusing on column h. any string found in column h the code will go through, Identify the last part of the string after the last "," and then replace the string in that cell with the sub-string it got.

I think this might be irrelevant to my question though because my problem is assigning the string in the cell to a new variable.

My issue is with the line highlighted here.

The error I am getting is:

Runtime-Error 1004, object-defined or application-defined error.

Please, next time use the appropriate tags. VB.NET is not VBA — Steve
– Steve, Commented Nov 7, 2015 at 14:23
@Bjørn-RogerKringsjå- I'm curious on how you would use a web search engine to find the OP's answer. Could you show an example? — Davesexcel
– Davesexcel, Commented Nov 7, 2015 at 14:59
@pnuts- that's right, the OP shows a lot of effort in the question, Bjorn's comment was not nice. — Davesexcel
– Davesexcel, Commented Nov 7, 2015 at 16:33

AdamRossWalker · Accepted Answer · 2015-11-07 14:28:03Z

3

Your program is looking for some variable or token called "h". As there isn't one, the compiler is complaining. Cells takes integers for the row and column, not the column letters. So if you replace h with 8 (the 8th column), it should solve this problem.

Update

Sorry, I think you also want Cells(i, 8) as the syntax is Cells(RowNumber, ColumnNumber).

answered Nov 7, 2015 at 14:28

AdamRossWalker

3242 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Davesexcel Over a year ago

Would there not be an error for i as well, can't have a row 0

Fnaxiom · Accepted Answer · 2015-11-07 17:20:36Z

1

First, the value of row i in column j would be Cells(i,j).

Second, you need to declare h as a column or as constant ...

Third, I am not sure about looping variable declare as in this way

Do While CONDITION
      Dim a as string
Wend

I don't think it would work.

answered Nov 7, 2015 at 17:20

Fnaxiom

3961 silver badge8 bronze badges

Collectives™ on Stack Overflow

Error reading strings from cells

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related