Vba loop through 2 columns

[Flowcentric]

Hi

I am creating a xml file from excel using vba. I have a list of stockcodes in a column and list of Warehouses in a column .
Each stock code must be attached to all warehouse. I want the code to selected the first stock code in column A and create the xml stockcode warehouse combination then move to A2 and then repeat then repeat the process until cell in the stockcode column = "" (so one stockcode many warehouses) Many thanks for your assistance

Here is the code below. I am not sure how to loop correctly

Private Sub WarehouseWHXM()
Dim Row1
Dim STOCK
Dim Wh
Dim ROW


Document = Document & "<setupinvwarehouse>"


Row1 = 7
ROW = 2


 
Do While Range("AH" & ROW).Text <> ""
Do While Range("A" & Row1).Text <> ""


Document = Document & "<item>"
Document = Document & "<key>"
Document = Document & "<stockcode>" & Range("A" & Row1).Text & "</stockcode>"
Document = Document & "<warehouse>" & Range("AH" & ROW).Text & "</warehouse>"
Document = Document & "</key>"
Document = Document & "<costmultiplier>1.10</costmultiplier>"
Document = Document & "<unitcost>10.00</unitcost>"
Document = Document & "</item>"
Row1 = Row1 + 1
Loop
ROW = ROW + 1
Loop
Document = Document & "</setupinvwarehouse>"


'Create file
'
   Dim FSO
   Dim FSOFile
   Set FSO = CreateObject("Scripting.FileSystemObject")
   'Set FSOFile = FSO.CreateTextFile("\\Tevdbn-app-01\SYSPRO7\DFM\GlUpdate\GLChanges.xml")
   Set FSOFile = FSO.CreateTextFile("C:\New folder\new.xml")
   
    
  FSOFile.WriteLine (Document)
  FSOFile.Close


 'Housekeeping
 


Set FSO = Nothing
Set FSOFile = Nothing
Document = ""

End Sub

 

[dchaney]

Here try this, let me know if I got it a bit closer... Think I understand what your looking for...

Private Sub WarehouseWHXM()

Dim rStock As Long, rWare As Long
Dim FSO As Object, FSOFile

Document = Document & "<setupinvwarehouse>"

rStock = 7
rWare = 2

Do While Range("A" & rStock).Text <> ""

   Do While Range("AH" & rWare).Text <> ""
        
        Document = Document & "<item>"
        Document = Document & "<key>"
        Document = Document & "<stockcode>" & Range("A" & rStock).Text & "</stockcode>" 
        Document = Document & "<warehouse>" & Range("AH" & rWare).Text & "</warehouse>" 
        Document = Document & "</key>" 
        Document = Document & "<costmultiplier>1.10</costmultiplier>" 
        Document = Document & "<unitcost>10.00</unitcost>" 
        Document = Document & "</item>" 
        rWare = rWare + 1 
       Loop

    rWare = 2
    rStock = rStock + 1

Loop 

Document = Document & "</setupinvwarehouse>"

'Create file
Set FSO = CreateObject("Scripting.FileSystemObject")
'Set FSOFile = FSO.CreateTextFile("\\Tevdbn-app-01\SYSPRO7\DFM\GlUpdate\GLChanges.xml")
Set FSOFile = FSO.CreateTextFile("C:\New folder\new.xml")

FSOFile.WriteLine (Document)
FSOFile.Close

'Housekeeping
Set FSO = Nothing
Set FSOFile = Nothing
Document = ""

End Sub

</div>

 

[Flowcentric]

YOU ARE A LIFESAVER. Thank you so much it works!!

 

[dchaney]

My pleasure Have a wonderful rest of your day...

 

[dchaney]

My pleasure Have a wonderful rest of your day...