| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next column
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Next column
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 -- Regards, Tom Ogilvy "Tom" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Next column
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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Tom" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Next column
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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Tom" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Next column
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). -- Regards, Tom Ogilvy "Tom" wrote in message ... 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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Tom" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Next column
I really appreciate your help. Thankyou very much...worked just fine!
Tom "Tom Ogilvy" wrote in message ... 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). -- Regards, Tom Ogilvy "Tom" wrote in message ... 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 "Tom Ogilvy" wrote in message ... 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 -- Regards, Tom Ogilvy "Tom" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
| Calculating column A * column B = Results in another column | Excel Worksheet Functions | |||
| Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
| Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Linear Mode
