Question in a function cell to cell

  • Thread starter Thread starter keawee
  • Start date Start date
K

keawee

Hello,

I have a question about VBA in Excel. I received from Access of the
data that I insert in Excel. I use a formula with a IF like below. I
place it in the B24 cell then I recopy it in an automatic way to cell
IV24 but I cannot use in IF for saying if I have anything, I do
anything if not make calculation and frames the cell by a border.

Code:
--------------------------------------------------------------------------------

IF(B5="";"";b14*100/b22)
--------------------------------------------------------------------------------



How to do in VBA this:
I place myself in the B24 cell, If B5 and empty then I do anything if
not I make B14*100/B22 and I put a border at my cell then I shift of a
cell, I place myself in C24 and if C5 is empty I do anything if not I
make C14*100/C22 then I frame my cell and so on.

I would like to make a procedure which automatically starts reception
of the data of Access.

Could you help me on this problem.

Keawee
 
Will this work for you? You can also choose appropriate
variable names to replace the hardcoded rows I used.

Sub Macro1()
Dim iColumn As Integer
Dim iMaxCol As Integer
Dim lCalc As Long

iColumn = 2
iMaxCol = Range("IV1").Column
Do Until iColumn > iMaxCol
If Cells(5, iColumn).Value <> "" Then
If Cells(22, iColumn).Value = 0 Then
Cells(24, iColumn).Value = 0
Else
lCalc = Cells(14, iColumn).Value * 100
lCalc = lCalc / Cells(22, iColumn).Value
Cells(24, iColumn).Value = lCalc
End If
Cells(24, iColumn).Select
Call DrawLine(xlEdgeLeft)
Call DrawLine(xlEdgeTop)
Call DrawLine(xlEdgeBottom)
Call DrawLine(xlEdgeRight)
End If
iColumn = iColumn + 1
Loop
End Sub

Sub DrawLine(aEdge)
With Selection.Borders(aEdge)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
 
Hi keawee,

You can try this:

Sub YesNoCalc()
On Error goto ErrInProcedure

Dim c As Integer

For c = 2 To 256

If Cells(5, c) <> "" Then
Cells(24, c) = (Cells(14, c) * 100) / (Cells(22, c))
Cells(24, c).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End If

Next c

Exit sub

ErrInProcedure:
MsgBox Err.Description


End Sub


You must make sure that Row 22 contains a number greater than zero,
else you will get an error.
 
Back
Top