Loop through 3 columns to get values 2

[jazminecat23]

Hi all -

I'm working on a bit of code to loop through values on one worksheet, and enter them into another worksheet. This is part of a larger function that will then save each set of values off in a separate worksheet. Right now, I'm just trying to get the loop to work, so i'm using a msgbox to watch it.

My values on Sheet 1 are:

Alpha 00001 510 FEB07MER510
Bravo 00001 520 FEB07MER520
Charlie 00001 540 FEB07MER540
Delta 00001 560 FEB07MER560
Echo 00001 590 FEB07MER590
Foxtrot 00001 3300 FEB07MER3300
Golf 00001 3310 FEB07MER3310
Hotel 00001 3320 FEB07MER3320
India 00001 2600 FEB07MER2600
Juliet 00001 2620 FEB07MER2620

Right now, my code is:

Dim r As Range
For Each r In Sheets("DistList").Range(Sheets("DistList").[A1], Sheets("DistList").[A1].End(xlDown))
  sDept = r.Value
  nVal1 = r.Offset(0.1).Value
  nVal2 = r.Offset(0.2).Value
  sName = r.Offset(0.3).Value
MsgBox nVal2

I got this far with Skip's help on my larger module. I'm using this to troubleshoot this module, in which I have code which takes the values in colums A and plugs them into my other spreadsheet and then saves each one as a separate workbook. In the above example, I cannot make the nval, nval2, and sName return the right things. They should return the values in the columns B, C, and D, respectively. Right now, as written, the msgbox loops through the values in column A, instead of column C. Any insight is greatly appreciated.

 

[PHV]

nVal1 = r.Offset(0[!], [/!]1).Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat23]

Thanks, that did the trick.