Next column

[Tom]

I am using a loop and after walking thru "x" numbers of rows. When I have
walked thru the rows I do the same thing again but I want to move to the
next column and grab the next column of data with the I want to move to the
next column. How could I accomplish this?

Much thanks in advance!

Tom

Do While Len(Range("A" & r).Formula) > 0 And r < 43
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldNameN") = Range("B3").Value
.Fields("FieldNameN") = Range("e17").Text
.Fields("FieldNameN") = Range("A" & r).Value
.Fields("FieldNameN") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' After here I would like to change the column to the next column
.Fields("FieldNameN") = Range("D" & r).Value
.Fields("FieldNameN") = Range("E" & r).Value

' add more fields
.Update ' stores the new record
End With
r = r + 1 ' next row

Loop

 

[Tom Ogilvy]

You have hard coded column references - what determines the next column.
You are working in column A through E of the current row. What determines
that you want to look at a different column rather than D and/or E. Which
column do you want to look at?

Do While Len(cells(r,i).formula) > 0 And r < 43
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldNameN") = Range("B3").Value
.Fields("FieldNameN") = Range("e17").Text
.Fields("FieldNameN") = Range("A" & r).Value
.Fields("FieldNameN") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' After here I would like to change the column to the next column
.Fields("FieldNameN") = Range("D" & r).Value
.Fields("FieldNameN") = Range("E" & r).Value

' add more fields
.Update ' stores the new record
End With
r = r + 1 ' next row

Loop

 

[Tom]

Thanks
The Hard coded ranges are for static values that are the same in each record
(the name of each package). The first four colums in the row are included
in every recordset insert. I then want to get the values from a column.
The type, vendor, currency and rate columns are included in each record set.
Pack1 is a different recordset then pack2 etc.

Does this make more sense?
Thanks
Tom

row a row b
row c row d row e row
f row g row h
type vendor
currency rate pack1 pack2
pack3 pack4
accom Australia AUD 0.67 2,231.23 1,115.61
1,115.61 1,115.61
landc Australia AUD 0.67 7,783.11 4,554.58
3,610.06 3,039.05
reg-room Australia AUD 0.67 187.50 93.75
93.75 93.75
d-room Australia AUD 0.67 202.50 101.25
101.25 101.25


You have hard coded column references - what determines the next column.
You are working in column A through E of the current row. What determines
that you want to look at a different column rather than D and/or E. Which
column do you want to look at?

Do While Len(cells(r,i).formula) > 0 And r < 43
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldNameN") = Range("B3").Value
.Fields("FieldNameN") = Range("e17").Text
.Fields("FieldNameN") = Range("A" & r).Value
.Fields("FieldNameN") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' After here I would like to change the column to the next column
.Fields("FieldNameN") = Range("D" & r).Value
.Fields("FieldNameN") = Range("E" & r).Value

' add more fields
.Update ' stores the new record
End With
r = r + 1 ' next row

Loop

 

[Tom]

Thanks
The Hard coded ranges are for static values that are the same in each record
(the name of each package). The first four colums in the row are included
in every recordset insert. I then want to get the values from a column.
The type, vendor, currency and rate columns are included in each record set.
Pack1 is a different recordset then pack2 etc.

Does this make more sense?
Thanks
Tom

row a row b row c row d row e row f
row g row h
type vendor currency rate pack1
pack2 pack3 pack4

accom
Australia AUD 0.67 2,231.23 1,115.61
1,115.61 1,115.61
L Costs Australia AUD 0.67 7,783.11 4,554.58
3,610.06 3,039.05
Preroom Australia AUD 0.67 187.50 93.75
93.75 93.75
DRoom Australia AUD 0.67 202.50 101.25
101.25 101.25



You have hard coded column references - what determines the next column.
You are working in column A through E of the current row. What determines
that you want to look at a different column rather than D and/or E. Which
column do you want to look at?

Do While Len(cells(r,i).formula) > 0 And r < 43
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldNameN") = Range("B3").Value
.Fields("FieldNameN") = Range("e17").Text
.Fields("FieldNameN") = Range("A" & r).Value
.Fields("FieldNameN") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' After here I would like to change the column to the next column
.Fields("FieldNameN") = Range("D" & r).Value
.Fields("FieldNameN") = Range("E" & r).Value

' add more fields
.Update ' stores the new record
End With
r = r + 1 ' next row

Loop

 

[Tom Ogilvy]

Do While Len(cells(r,i).formula) > 0 And r < 43
' repeat until first empty cell in column A
for i = 1 to 4
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldNameN") = Range("B3").Value
.Fields("FieldNameN") = Range("e17").Text
.Fields("FieldNameN") = Range("A" & r).Value
.Fields("FieldNameN") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
.Fields("FieldNameN") = Range("D" & r).Value
.Fields("FieldNameN") = Range("E" & r).Offset(0,i-1).Value

' add more fields
.Update ' stores the new record
End With
Next i
r = r + 1 ' next row

Loop

Would add a record with the fifth item being either pack1, pack2, pack3 or
pack4 (so for each row, 4 records would be added).

 

[Tom]

I really appreciate your help. Thankyou very much...worked just fine!
Tom


Do While Len(cells(r,i).formula) > 0 And r < 43
' repeat until first empty cell in column A
for i = 1 to 4
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldNameN") = Range("B3").Value
.Fields("FieldNameN") = Range("e17").Text
.Fields("FieldNameN") = Range("A" & r).Value
.Fields("FieldNameN") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
.Fields("FieldNameN") = Range("D" & r).Value
.Fields("FieldNameN") = Range("E" & r).Offset(0,i-1).Value

' add more fields
.Update ' stores the new record
End With
Next i
r = r + 1 ' next row

Loop

Would add a record with the fifth item being either pack1, pack2, pack3 or
pack4 (so for each row, 4 records would be added).