View Full Version : Solved: macro doubt
Ismael
11-24-2005, 08:01 AM
Hi to all in the forum,
I have a macro to calculate a array formula I'm sure that this macro isn't very nice but it works....
So my problem now is:
As you guys can see in the code bellow the array is executes until cell H100, but what I want to do is execute this formula until the last content of column b, imagine that my data finish in cell B10, I don't nedd to calculate the formula until H100, just to H10.
So if you guys can help me I really appreciate.
Best regards,
Ismael
Private Sub CommandButton2_Click()
Dim pergunta As String
pergunta = InputBox("Se pretende obter os resultados introduza a letra C, se Pretende apagar introduza a letra A")
If (pergunta = "C") Then
Range("H6").Select
Selection.FormulaArray = _
"=RC[-1]-SUM((IF((RC[-3]=R6C2:R100C2)*(RC[-2]=R6C3:R100C3),R6C4:R100C4)))"
Selection.AutoFill Destination:=Range("H6:H100"), Type:=xlFillDefault
Range("H6:H100").Select
Range("H6").Select
Else:
Range("H6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("H6").Select
End If
End Sub
Bob Phillips
11-24-2005, 09:01 AM
Hi to all in the forum,
I have a macro to calculate a array formula I'm sure that this macro isn't very nice but it works....
So my problem now is:
As you guys can see in the code bellow the array is executes until cell H100, but what I want to do is execute this formula until the last content of column b, imagine that my data finish in cell B10, I don't nedd to calculate the formula until H100, just to H10.
So if you guys can help me I really appreciate.
Best regards,
Ismael
Private Sub CommandButton2_Click()
Dim pergunta As String
pergunta = InputBox("Se pretende obter os resultados introduza a letra C, se Pretende apagar introduza a letra A")
If (pergunta = "C") Then
Range("H6").Select
Selection.FormulaArray = _
"=RC[-1]-SUM((IF((RC[-3]=R6C2:R100C2)*(RC[-2]=R6C3:R100C3),R6C4:R100C4)))"
Selection.AutoFill Destination:=Range("H6:H100"), Type:=xlFillDefault
Range("H6:H100").Select
Range("H6").Select
Else:
Range("H6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("H6").Select
End If
End Sub
Private Sub CommandButton2_Click()
Dim pergunta As String
Dim iLastRow As Long
pergunta = InputBox("Se pretende obter os resultados introduza a letra C," & vbNewLine & _
"se Pretende apagar introduza a letra A")
With Range("H6")
If (pergunta = "C") Then
iLastRow = Range("B6").End(xlDown).Row
.FormulaArray = _
"=RC[-1]-SUM((IF((RC[-3]=R6C2:R" & iLastRow & _
"C2)*(RC[-2]=R6C3:R" & iLastRow & _
"C3),R6C4:R" & iLastRow & "C4)))"
.AutoFill Destination:=Range("H6:H" & iLastRow), Type:=xlFillDefault
Else
.Resize(iLastRow - .Row + 1).ClearContents
End If
End With
End Sub
Ismael
11-24-2005, 09:24 AM
Hi XLD,
In first place thanks for the reply.
But it seems that we have 2 problems....
the 1? it's my blame...I didn't explation well the disposition of the data in column B, that's why the code doesn't work, I have empty cells between cell whit data on column B (you can see this on picture calculated on attach), so what I want is every time that I have a value on column B make the array formula.
The 2? I think is something that you type, only happens when I intoduze the letter A, you can see this error on the picture error on attach.
So if you can help me once more I will be quite thankful.
Best regards,
Ismael
Bob Phillips
11-24-2005, 09:41 AM
Private Sub CommandButton2_Click()
Dim pergunta As String
Dim iLastRow As Long
pergunta = InputBox("Se pretende obter os resultados introduza a letra C," & vbNewLine & _
"se Pretende apagar introduza a letra A")
With Range("H6")
If (pergunta = "C") Then
iLastRow = Range("B" & Rows.Count).End(xlUp).Row
.FormulaArray = _
"=RC[-1]-SUM((IF((RC[-3]=R6C2:R" & iLastRow & _
"C2)*(RC[-2]=R6C3:R" & iLastRow & _
"C3),R6C4:R" & iLastRow & "C4)))"
.AutoFill Destination:=Range("H6:H" & iLastRow), Type:=xlFillDefault
Else
iLastRow = Range("H" & Rows.Count).End(xlUp).Row
.Resize(iLastRow - 5).ClearContents
End If
End With
End Sub
Ismael
11-24-2005, 10:14 AM
Hi XLD,
Thanks for your precious help.
Best regards,
Ismael
Ismael
11-24-2005, 11:04 AM
Hi again XLD,
If isn't abuse I would like to now if you can arrange the macro to do excatly the same thing that already does in column H on column P, X, AF, AN, AV, BD, BL, BT, CB, CJ and CR, probably this will give to much work, maybe if you do just for column P and explain me how to expand is better. I try to do it, but I can't.
If you can please give a hand on this.
Best Regards,
Ismael
Bob Phillips
11-24-2005, 12:48 PM
Private Sub CommandButton2_Click()
Dim pergunta As String
Dim iLastRowB As Long
Dim iLastRow As Long
Dim aryColumns
Dim i As Long
aryColumns = Array("H", "P", "X", "AF", "AN", "AV", "BD", "BL", "BT", "CB", "CJ", "CR")
pergunta = InputBox("Se pretende obter os resultados introduza a letra C," & vbNewLine & _
"se Pretende apagar introduza a letra A")
iLastRowB = Range("B" & Rows.Count).End(xlUp).Row
For i = LBound(aryColumns) To UBound(Columns)
With Range(aryColumns(i) & "6")
If (pergunta = "C") Then
.FormulaArray = _
"=RC[-1]-SUM((IF((RC[-3]=R6C2:R" & iLastRow & _
"C2)*(RC[-2]=R6C3:R" & iLastRowB & _
"C3),R6C4:R" & iLastRowB & "C4)))"
.AutoFill Destination:=Range(aryColumns(i) & "6:" & aryColumns(i) & iLastRowB), _
Type:=xlFillDefault
Else
iLastRow = Range(aryColumns(i) & Rows.Count).End(xlUp).Row
.Resize(iLastRow - 5).ClearContents
End If
End With
Next i
End Sub
Ismael
11-25-2005, 03:06 AM
Hi XLD,
In first place thank you very much for all the help that you already give me in this thread, but I will ask you one more thing if you don?t mind ok?
So I already see that you change the code of the macro to do exactly what I ask you to do, and once more I thank you for this, but unfortunately I forgot to tell you one thing that is:
Regarding the formula in cell H6 we have:
={G6-SUM((IF((E6=$B$6:$B$7)*(F6=$C$6:$C$7);$D$6:$D$7)))}
So the difference between what I want and what you have just done is simple but probably complicate to do, but let see.
When you make the formula on column P the reference that are locked (with the $) doesn?t change and I need to change this, so in resume the formula on column P have to be like this:
={O6-SUM((IF((M6=$J$6:$J$7)*(N6=$K$6:$K$7);$L$6:$L$7)))}
On column X like this:
={W6-SUM((IF((U6=$R$6:$R$7)*(V6=$S$6:$S$7);$T$6:$T$7)))}
And do the same thing in the others columns, probably this isn?t easy to do, but if you can help me I will be quite thankful.
Kind regards,
Ismael
Bob Phillips
11-25-2005, 03:19 AM
Private Sub CommandButton2_Click()
Dim pergunta As String
Dim iLastRowB As Long
Dim iLastRow As Long
Dim aryColumns
Dim i As Long
aryColumns = Array("H", "P", "X", "AF", "AN", "AV", "BD", "BL", "BT", "CB", "CJ", "CR")
pergunta = InputBox("Se pretende obter os resultados introduza a letra C," & vbNewLine & _
"se Pretende apagar introduza a letra A")
iLastRowB = Range("B" & Rows.Count).End(xlUp).Row
For i = LBound(aryColumns) To UBound(aryColumns)
With Range(aryColumns(i) & "6")
If (pergunta = "C") Then
.FormulaArray = _
"=RC[-1]-SUM((IF((RC[-3]=R6C[-6]:R" & iLastRowB & _
"C[-6])*(RC[-2]=R6C[-5]:R" & iLastRowB & _
"C[-5]),R6C[-4]:R" & iLastRowB & "C[-4])))"
.AutoFill Destination:=Range(aryColumns(i) & "6:" & aryColumns(i) & iLastRowB), _
Type:=xlFillDefault
Else
iLastRow = Range(aryColumns(i) & Rows.Count).End(xlUp).Row
.Resize(iLastRow - 5).ClearContents
End If
End With
Next i
End Sub
Ismael
11-25-2005, 03:40 AM
Hi XLD,
In fact you are the best:clap: , now i just a to change one thing, the code that you type is starting on row 5 instead of row 6, I try to change 2 parameters were you have put 6 I put 7, but didn't work, so maybe you can arrange this to me. You can see this on picture attach.
Kind regards,
Ismael
Bob Phillips
11-25-2005, 03:51 AM
Private Sub CommandButton2_Click()
Const StartRow As Long = 5 'modify this to suit
Dim pergunta As String
Dim iLastRowB As Long
Dim iLastRow As Long
Dim aryColumns
Dim i As Long
aryColumns = Array("H", "P", "X", "AF", "AN", "AV", "BD", "BL", "BT", "CB", "CJ", "CR")
pergunta = InputBox("Se pretende obter os resultados introduza a letra C," & vbNewLine & _
"se Pretende apagar introduza a letra A")
iLastRowB = Range("B" & Rows.Count).End(xlUp).Row
For i = LBound(aryColumns) To UBound(aryColumns)
With Range(aryColumns(i) & StartRow)
If (pergunta = "C") Then
.FormulaArray = _
"=RC[-1]-SUM((IF((RC[-3]=R" & StartRow & "C[-6]:R" & iLastRowB & _
"C[-6])*(RC[-2]=R" & StartRow & "C[-5]:R" & iLastRowB & _
"C[-5]),R" & StartRow & "C[-4]:R" & iLastRowB & "C[-4])))"
.AutoFill Destination:=Range(aryColumns(i) & StartRow & ":" & _
aryColumns(i) & iLastRowB), _
Type:=xlFillDefault
Else
iLastRow = Range(aryColumns(i) & Rows.Count).End(xlUp).Row
.Resize(iLastRow - 5).ClearContents
End If
End With
Next i
End Sub
Ismael
11-25-2005, 03:58 AM
Hi XLD,
ok this way is more easy because I can change the row were I want to start.
But the code have an error, you can see this error in picture attach.
Best regards,
Ismael
Bob Phillips
11-25-2005, 04:39 AM
Hi XLD,
ok this way is more easy because I can change the row were I want to start.
But the code have an error, you can see this error in picture attach.
Best regards,
Ismael
Ismael,
I didn't get that problem. Can ou post your workbook for me to check?
Ismael
11-25-2005, 05:04 AM
XLD,
In fact it seems that the error go away, but a strange thing happens.
In the attach file you will see that I only have data in columns AH:AM, and if you run the macro just like that the macro start on row 5, but if you copy the data that are in AH:AM to columns B:G the macro starts at row 6, I don't now why.
And if you choose the letter A in order to delete the content you will receive a message of error.
If you can please take a look at it.
Best regards,
Ismael
Ismael
11-25-2005, 05:11 AM
Hi XLD,
Sorry the file that I send doesn't have the correct code, in this file I was trying to see what was the error, in the file that I will post now is your correct code.
Best regards,
Ismael
Bob Phillips
11-25-2005, 05:40 AM
Hi XLD,
Sorry the file that I send doesn't have the correct code, in this file I was trying to see what was the error, in the file that I will post now is your correct code.
Best regards,
Ismael
You have StartRow set to 6!
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.