Question in a function cell to cell

[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

 

[Dizzy]

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



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.

 

[keawee]

Thanks Dizzy for your answer,

I will try your code tomorrow.

Thanks

Keawee

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

--

----------------------

--

----------------------



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



------------------------------------------------
[/url]
~~ View and post usenet messages directly from http://www.ExcelForum.com/

.

.

 

[zantor]

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.