MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can someone translate this to VB for me?

Posted by Rob on July 31, 2001 12:42 PM

This is what im looking for in VB if someone would be so nice, please and thank you :)

Id like this to apply for all cells which are NOT blank


If $A1 not= "" then
format cell with a border and bold
End If

also, for the "C" column, i would like a formula inserted ONLY if A1 is NOT blank so...

If $A1 not= "" then
$C1 = $A1 + $B1
End If

I appreciate any help. I'm doing this in hopes to reduce file size, and learn VB some :) Thanks.

Posted by Quarlton on August 01, 2001 1:45 AM

Hi Rob ,
I have made a kind of macro which would interest you

sub change()
Dim CurrentLine as integer
CurrentLine = 2
'(if you have header than =1)
While cells(CurrentLine,1)<>""
'Here = a2 to begin is différent to blanck
'Here find in the help for the form or property of a cell, sorry
'To go to the following line
'end of the loop
MsgBox "Finito!", vbExclamation
'Pop up to inform you when it is finished !
End sub

Posted by faster on August 01, 2001 9:55 AM

Rob I think this my help you

Sub Rob()
'start cell

'find last row of data
Dim LastRow
LastRow = ActiveCell.SpecialCells(xlLastCell).Row

Do While Selection.Row < LastRow + 1
If Selection <> "" Then
'apply border
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Font.Bold = True
'add formula to "C"
Selection.Offset(0, 2).FormulaR1C1 = "=RC[-2]+RC[-1]"
End If
'move down one row
Selection.Offset(1, 0).Select

'clean end

End Sub

Posted by Rob on August 01, 2001 1:19 PM