CODEWITHSUNDEEP
excelvba
Ales Hamerle
Ales Hamerle

Reputation: 1

Getting value of specific column for each row

I need to write script which allows me import data in xml format. It generates block of code I need for every order except variables. I need to go through all rows in range and get values from column "G". Problem is it gets only value of first row in range. 

Dim rng As Range, row As Range
Set rng = Range(Range("G11"), Range("G11").End(xlDown))
For Each row In rng.Rows
Dim partner_id As String
partner_id = Cells(rng.row, 7).Value
line9 = "<typ:id>" & partner_id & "</typ:id>" & vbNewLine

...

I'm not a programmer as you can see, but I really need to get that partner_id. I would appreciate any suggestions.

Upvotes: 0

Views: 1649

Answers (1)

YowE3K
YowE3K

Reputation: 23994

You really should avoid using variable names which are the same as the common Methods used by Excel objects so, for instance, change row to be myRow perhaps. That would then highlight what I think was causing your issue - the use of rng.row which, with a renamed variable, you would probably have written as rng.myRow - which would then give a compile error.

rng.row is simply returning the row number of the first cell in rng, and has nothing to do with your variable called row.

Dim rng As Range, myRow As Range
Set rng = Range(Range("G11"), Range("G11").End(xlDown))
'There's no need to use "rng.Rows" when each row is a single cell anyway
For Each myRow In rng
    Dim partner_id As String
    'because myRow is a single cell, we can just use its Value
    partner_id = myRow.Value
    line9 = "<typ:id>" & partner_id & "</typ:id>" & vbNewLine

    ...

FWIW - leaving your variable names and coding style unchanged, you could have fixed the code by using row.Row instead of rng.Row, i.e.

Dim rng As Range, row As Range
Set rng = Range(Range("G11"), Range("G11").End(xlDown))
For Each row In rng.Rows
Dim partner_id As String
partner_id = Cells(row.Row, 7).Value
line9 = "<typ:id>" & partner_id & "</typ:id>" & vbNewLine

...

Upvotes: 1

Related Questions