CODEWITHSUNDEEP
excelvba
kazSone
kazSone

Reputation: 105

concatenation until empty cell

I've made a code to concatenate 3 columns namely YR which corresponds to cell A2, JJ which corresponds to cell B2 and YA which corresponds to cell D2. when i clicked a button i want it display the answer to G2. here's my code:

Sub Button1_Click()
Dim YR As String
Dim JJ As String
Dim YA As String
Dim RF As String

YR = Range("A2")
JJ = Range("B2")
YA = Range("D2")
RF = YR & "." & JJ & "." & YA
Range("G2") = RF

End Sub

my problem now is how can i made this code to work until there's a blank cell?? i've search a code regarding this which is:

Range("A1").Select 
Do Until IsEmpty(ActiveCell.Value) 
‘codes here
ActiveCell.Offset(1,0).Select 
Loop

how can i made my code and thanks in advanced!

Upvotes: 1

Views: 2417

Answers (2)

Santosh
Santosh

Reputation: 12353

Try below code if you don't wanna loop.

Sub Button1_Click()

    Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("G2:G" & lastRow).FormulaR1C1 = "=RC[-6] & ""."" & RC[-5] & ""."" & RC[-3]"

End Sub

enter image description here

Upvotes: 3

NickSlash
NickSlash

Reputation: 5077

Assuming I've understood your question, the following code might help (I've not tested it though)

Sub catrow(row as integer)
Dim YR As String
Dim JJ As String
Dim YA As String
Dim RF As String

YR = Range("A" & row).value2
JJ = Range("B" & row).value2
YA = Range("D" & row).value2
RF = YR & "." & JJ & "." & YA
Range("G" & row) = RF
End Sub

Sub Button1_Click()
Dim Target As Range
Dim Row As Integer
Row = 1
Set Target = ActiveSheet.Range("A" & Row)
Do Until IsEmpty(Target.Value)
    catrow(row)
    Set Target = Target.Offset(RowOffset:=1)
Loop
End Sub

Upvotes: 0

Related Questions