changing range value in run time

[jsimha]

Dear All,

I have a simple macro below :

Sub Macro2()

ActiveCell.Offset(-5, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "TASK"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],""-"",RC[-2])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5")

End Sub

Above code does following :

- go to col-d , types TASK
- perform concat on col-a, col-b text and returns value
- it works fine as long as i have 5 records, where is there increase in no of records it gives error.

How to give the cell range based on no records present in data

regards,
jsimha

[OllieB]

Hi jsimha,

the code is strangely written. for example the first statement will only work if the cursor is in row 6 (i.e. ActiveCell), because you are telling the system to go up 5 rows from the current position and one to the right. So for you to put the value "TASK" in column D the cursor must be in column C and at least row 6, and the row determined where the value TASK will be written.

ActiveCell.Offset(-5, 1).Range("A1").Select

Would you be able to describe in words what you are trying to achieve here?

[jsimha]

Hi Olaf,

I am trying to concatenation in the excel via macro

See the excel workbook attached, in the column-d from row 42 till the down i see the formula concatenate exist.
Which i do not want. I want concatenate should perform only till the data exist.

I don't want range to be hard coded in macro. is there anyway it calculated automatically?

Regards,
Jay

[OllieB]

or write the formula assignment line as if you find that more readable

.Cells(lngRowNumber, "D").Formula = Replace("=CONCATENATE(A$1,B$1)", "$1", lngRowNumber)

[OllieB]

Try (after clearing all old entries)

'####################################################################################################################################
'# introduce concatenation formula for populated rows
'####################################################################################################################################
     Public Sub IntroduceFormula()
          '# declare
               Dim lngRowNumber As Long
          '# process all rows from row 2 till the last row entry in column A
               With ThisWorkbook.Worksheets("Sheet1")
                    For lngRowNumber = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
                         .Cells(lngRowNumber, "D").Formula = "=CONCATENATE(A" & lngRowNumber & ",B" & lngRowNumber & ")"
                    Next lngRowNumber
               End With
     End Sub

[jsimha]

Thanks Olaf, working fine.
have a nice day.

[arlu1201]

jsimha,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here