Solved: macro doubt

[Ismael]

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]

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]

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]

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]

Hi XLD,

Thanks for your precious help.

Best regards,

Ismael

[Ismael]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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!