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
so..
If $A1 not= "" then
format cell with a border and bold
Else
nothing
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
Else
Nothing
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
CurrentLine=Currentline+1
'To go to the following line
wend
'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
Range("A1").Select
'find last row of data
Dim LastRow
LastRow = ActiveCell.SpecialCells(xlLastCell).Row
'loop
Do While Selection.Row < LastRow + 1
'test
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
'bold
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
Loop
'clean end
Range("A1").Select
End Sub
Posted by Rob on August 01, 2001 1:19 PM
Thanks!!
