PDA

View Full Version : [SOLVED] Problem with code. Please help.



pegbol
02-15-2005, 05:43 AM
Please, would anyone can help me with the next code?. It gives me an error. :banghead:


Sub eeff()
Dim lastR As Long, ws3 As Worksheet
Set ws3 = Sheets("consolidado")
lastR = Range("b65536").End(xlUp).Row
Application.ScreenUpdating = False
With ws3
With Range("k8:k" & lastR)
'FORMULA COLUMN K
.FormulaR1C1 = "=IF(OR(RC[-2]=""ACT"",RC[-2]=""EGR"")," & _
"IF(RC[-7]>RC[-6],RC[-7]-RC[-6],RC[-7]-RC[-6])," & _
"IF(RC[-6]>RC[-7],RC[-7]-RC[-6],RC[-7]-RC[-6]))"
.FormulaR1C1 = .Value
'COLOR RED COLUMN K
If .Value < 0 And .Offset(0, -1).Value = "D" _ '<------ I THINK ERROR IS HERE
Or .Value >= 0.01 And .Offset(0, -1).Value = "H" Then
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
End If
End With
End With
Application.ScreenUpdating = True
Set ws3 = Nothing
End Sub



Thanks in advance for your kind assistance.
.
.

Jacob Hilderbrand
02-15-2005, 05:53 AM
With Range("k8:k" & lastR)
This is a range of cells. So you will have a problem when you try to use .Value.

Try something like this.


For i = 8 to lastR
With Range("K" & i)
'Your Code
End With
Next i

CBrine
02-15-2005, 07:20 AM
Jake,
I don't think that the problem. LastR is a Long that represents a row number.

lastR = Range("b65536").End(xlUp).Row

A portion is trying to act on a multiple range object as though it's a single value. I think he needs to cycle through the range, something like this.



Sub eeff()
Dim lastR As Long, ws3 As Worksheet, R as range
Set ws3 = Sheets("consolidado")
lastR = Range("b65536").End(xlUp).Row
Application.ScreenUpdating = False
With ws3
With Range("k8:k" & lastR)
'FORMULA COLUMN K
.FormulaR1C1 = "=IF(OR(RC[-2]=""ACT"",RC[-2]=""EGR"")," & _
"IF(RC[-7]>RC[-6],RC[-7]-RC[-6],RC[-7]-RC[-6])," & _
"IF(RC[-6]>RC[-7],RC[-7]-RC[-6],RC[-7]-RC[-6]))"
.FormulaR1C1 = .Value
End With
for each R in Range("k8:k" & lastR)
'COLOR RED COLUMN K
If r.Value < 0 And r.Offset(0, -1).Value = "D" _ '<------ I THINK ERROR IS HERE
Or r.Value >= 0.01 And r.Offset(0, -1).Value = "H" Then
r.Font.ColorIndex = 2
r.Interior.ColorIndex = 3
End If
Next
End With
Application.ScreenUpdating = True
Set ws3 = Nothing
End Sub


HTH
Cal

Jacob Hilderbrand
02-15-2005, 07:26 AM
A portion is trying to act on a multiple range object as though it's a single value. I think he needs to cycle through the range, something like this.

Isn't that what I said. :think: Where he is trying to check the .Value of his With Range he needs to loop through them instead.

CBrine
02-15-2005, 07:32 AM
Sorry Jake,
I didn't read it throughly.
:banghead: :bug:

pegbol
02-15-2005, 07:48 AM
.
.
That was it !!!!!. Now the code is working!!!!!:rotlaugh:
Thanks so much a lot DRJ.:bow:

A last favor, (I am a newbie in VBA). Is there any way to improve my code (I would like, it would be much faster).

regards,
Pedro
La Paz, BOLIVIA.

P.S.
What I am pretending to do with the code is:

After make the calculations with formula in columns K and L. Based on the value of each cell in columns K or L (wheter negative or positive). And based on the value of each cell in column J (wheter D or H). The respective cell in columns K or L would be red or not.

Example:
If K8 is: -10 , and J8 is: D. Result K8: color red.
If K8 is: 10 , and J8 is: H. Result K8: color red.

If K8 is: 10 , and J8 is: D. Result K8: no color.
If K8 is: -10 , and J8 is: H. Result K8: no color.



Sub eeff()
Dim lastR As Long, ws3 As Worksheet, i As Long
Set ws3 = Sheets("consolidado")
lastR = Range("b65536").End(xlUp).Row
Application.ScreenUpdating = False
With ws3
For i = 8 To lastR
With Range("k" & i)
'FORMULA COLUMN K
.FormulaR1C1 = _
"=IF(OR(RC[-2]=""ACT"",RC[-2]=""EGR""),IF(RC[-7]>RC[-6],RC[-7]-RC[-6],RC[-7]-RC[-6]),IF(RC[-6]>RC[-7],RC[-7]-RC[-6],RC[-7]-RC[-6]))"
.FormulaR1C1 = .Value
'COLOR RED COLUMN K
If .Value < 0 And .Offset(0, -1).Value = "D" _
Or .Value >= 0.01 And .Offset(0, -1).Value = "H" Then
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
End If
End With
With Range("l" & i)
'FORMULA COLUMN L
.FormulaR1C1 = _
"=IF(OR(RC[-3]=""ACT"",RC[-3]=""EGR""),IF(RC[-6]>RC[-5],RC[-6]-RC[-5],RC[-6]-RC[-5]),IF(RC[-5]>RC[-6],RC[-6]-RC[-5],RC[-6]-RC[-5]))"
.FormulaR1C1 = .Value
'COLOR RED COLUMN L
If .Value < 0 And .Offset(0, -2).Value = "D" _
Or .Value >= 0.01 And .Offset(0, -2).Value = "H" Then
.Font.ColorIndex = 2
.Interior.ColorIndex = 3
End If
End With
Next i
'SUBTOTALS
With Range("k" & lastR + 1)
.FormulaR1C1 = "=subtotal(9,r8c:r[-1]c)"
.AutoFill Destination:=Range("k" & lastR + 1 & ":l" & lastR + 1)
End With
With Range("k" & lastR + 1 & ":l" & lastR + 1)
.Borders(xlEdgeTop).LineStyle = xlcontinous
.Borders(xlEdgeTop).Weight = xlThin
.Font.Bold = True
.Borders(xlEdgeBottom).LineStyle = xlDouble
End With
Range("m1").Select
End With
Application.ScreenUpdating = True
Set ws3 = Nothing
End Sub

Jacob Hilderbrand
02-15-2005, 04:24 PM
If you have 4 or less formats that you want to use based on certain conditions, have a look at Contitional Formatting (Format | Conditional Formatting).


Select Column K



Format | Conditional Formatting



Formula Is




=And($K1=-10,$J1="D")



Format as needed



Add your other conditions and formats

pegbol
02-15-2005, 06:54 PM
Thanks so much Jake!!!

Your advice right to the point.

:thumb

regards,
Pedro.

Jacob Hilderbrand
02-15-2005, 06:57 PM
You're Welcome :beerchug:

Take Care