check for non-blank cells in Microsoft Excel VBA Programming

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rhodyman10@yahoo.com - 22 Jan 2006 01:48 GMT

I am working on a macro in Microsoft Excel 2000 vba.

I want to concatenate the two cells to the left of it.

This is what I have so far:

ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
Range("E4").Select
Selection.AutoFill Destination:=Range("E4:E6")
Range("E4:E6").Select
Range("E7").Select
End Sub

How do I run the same macro, but have it check to the cell left of it?

If the cell to the left of it has a value in it, I want it to
concatenate.

If the cell is a *null* or *blank cell*, I want the sub to stop.

How do I do it?

Thank you in advance for your help.

Reply to this Message

Yngve - 22 Jan 2006 13:17 GMT

Hi rhodyma
try this

Sub h()
If ActiveCell.Offset(0, -1) > "" Then
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
Range("E4").AutoFill Destination:=Range("E4:E6")
Range("E7").Select
Else
Exit Sub
End If
End Sub

if activecell = Range("E4") then cange it to
Range("E4") .Offset(0, -1) > "" Then
Range("E4").FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"

Regards Yngve

Reply to this Message

Tushar Mehta - 22 Jan 2006 13:49 GMT

Not quite sure how to interpret the conditional requirement but maybe
Example #4 in
Beyond Excel's recorder
http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

Signature

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

> I am working on a macro in Microsoft Excel 2000 vba.
>
[quoted text clipped - 19 lines]
>
> Thank you in advance for your help.

Reply to this Message