variables

[hardhat]

I have written a macro to process a number of files, import them and turn them into excel spreadsheets. I have acheived this using a loop and all works well. What I would like to do is name the sheet and the file using the customers name.

The loop value for example is:

top10 = 1

I have set the customers as such:

cust1 = bob
cust2 = john
cust3 = dave

I now need to call the customer name from within my loop but I'm not sure how to concatenate a tag with the contents of a variable:

Sheets(sheetname).Name = cust & top10

(this does not work but hopefully you can see what I am trying to acheive with the result being a variable cust1 giving a sheet name of bob)

I hope this makes sense.

[raypayette]

Try this:
Sub test()
cust1 = "Bob"
top10 = 1
Sheets("Sheet1").Name = cust1 & top10
End Sub

Note cust is not equal to cust1 !

[jtp]

Looks like he is trying to concatenate cust and top10 to use the cust1 variable. If he wants to loop this, I only know how through an array. An example:

Please Login or Register  to view this content.

HTH

[hardhat]

Hi JTP,

I think you have the right idea only I'm not sure how to build the array.

assignArray( )
Dim arr(10) as String
Dim top10 as Integer
arr(1) = "Bob"
arr(2) = "Fred"
arr(1) = "Jon"

Top10 = 1

Do While Top10 <= 3

saveto_file = arr(top10)

Top10 = Top10 + 1
Loop

Any help much appreciated.

[jtp]

Try this:

Please Login or Register  to view this content.