Variable in a function in a macro

[Tooch81]

I've been writing macros for a while and for some reason I can't get a varaible in my formula to work correctly. This is a very simple macro this is just going to concatenate two cells (one static, the other a variable) and it's not working right. I've tried everythign I can think of and I'm stuck.

Thanks,

-Jeff-

- ---------------------------------------------

count1 = 2
sheet = 7

Sheets(sheet).Select

Do While count1 < 15
Cells(1, count1).Select
ActiveCell.Formula = CONCATENATE(Cells(1, 1).Value, Cells(count1, 5).Value)
count1 = count1 + 1
Loop

- ---------------------------------------------

I have tried everything know of to get this to work with no luck
ActiveCell.Formula = "Concatenate(A1,B5)"
The above code is the only way I can get it to work, but I need the B5 to be a variable.

 

[Jon von der Heyden]

Welcome to the board

ActiveCell.Formula = "= CONCATENATE(" & Cells(1, 1).Address & "," & Cells(count1, 5).Address & ")"

 

[kpark91]

How about

ActiveCell.Formula = "CONCATENATE(" & Cells(1,1).Address & "," & Cells(count1,5).Address & ")"

?

 

[VoG]

Try

Do While count1 < 15
    Cells(1, count1).Formula = "=CONCATENATE(" & Cells(1, 1).Address & "," & Cells(count1, 5).Address & ")"
    count1 = count1 + 1
Loop

 

[Tooch81]

I have been reading this borad for a couple years now and have never had to sign up until today. With that being said, a 2 minute turn around is AMAZING!!!

You guys are the best and I didnt' realize that I could seperate the "" within a formula.

Thanks again.