Concatenation using For Next Loop

Bertie · Apr 26th 2006

Hello Everybody

Was after a bit a help if possible....

I'm getting my knickers in a twist over this and to make it more annoying I know that I'm doing something really stupid.

As part of a script I'm tring to concatenate the contents of column C with column H - the results to appear in column R. It will be an unknown and ever changing amount of rows.

I believe that it's the value for field 'NewField' that's causing most problems, but it could be other stuff.

Any help appreciated.

Thanks v much
B

Code

Dim I As Integer
Dim LastRow As Integer
Dim NewField As String


LastRow = ActiveSheet.Range("A4").End(xlDown).Row


For I = 4 To LastRow


NewField = ("R" & First)


NewField.Value = (("C" & First) & ("H" & First))


Next I

Display More

JMAN · Apr 26th 2006

Re: Concatenation using For Next Loop

I think you need to take a look at how you defined your variables first. I believe you want LastRow to be set as Long.

I can't really tell how NewField is coming into play as a string either.

**Ger Plante** · Apr 26th 2006

Re: Concatenation using For Next Loop

Does this do what youre looking for?

Code

Public Sub test()


Dim I As Long
Dim LastRow As Long
 
LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
 
With ActiveSheet
    For I = 4 To LastRow
        .Range("R" & I).Value = .Range("C" & I).Value & .Range("H" & I).Value
     
    Next I
End With


End Sub

Display More

?

Ger

**norie** · Apr 26th 2006

Re: Concatenation using For Next Loop

What's First?

It doesn't seem to be getting defined/declared anywhere.

Also there's no need for a loop.

Code

Dim LastRow As Integer
     
    LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
     
    With ActiveSheet.Range("R4:R" & LastRow)
        
        .Formula = "=C4 & H4"
             
        .Value = .Value
        
    End With

Display More

Bertie · Apr 26th 2006

Re: Concatenation using For Next Loop

Thanks people for replying. Ger Plante's code was what I was trying to achieve and it works so thank you.

Thanks also, Norie. My inefficiency duly noted ;-). Whenever I try some VBA I always go for the hardest solution....need more practice.

Thanks again.
B

**Ger Plante** · Apr 26th 2006

Re: Concatenation using For Next Loop

Thanks for posting back Bertie... practice makes perfect... stick at it.

For reference, Nories code is immeasurably more efficient, but as I say, thats what comes from practice.

Ger

Concatenation using For Next Loop

Participate now!

Share