Question in a function cell to cell
 

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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Question in a function cell to cell
 

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



-----Original Message-----
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.

Question in a function cell to cell
 

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

ErrInProcedu
MsgBox Err.Description


End Sub


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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/