Run time error 7. Out of memory error to correct

Due to no response, which is very rare, from the group I have shared the query with other forum.
https://forum.ozgrid.com/forum/index...s-with-a-code/

bebo. I got the formulas in KA right. I need to resize the columns with formula with column A. I thought this line will fill the rows below but it is not. Maybe I didn't understand that line.

Please Login or Register  to view this content.

I will try to create the same for Extract B2B but still the formulas columns need to be resized with column A.

Sorry. Shared the wrong workbook. This is the updated one.

Can you please change this line LastRowKAB2B = 5925 as there will be different rows in different data.?
something like LastRow = Sheets("KA").Range("A" & Rows.Count).End(xlUp).Row
It is a line from some other application and the range needs to be corrected.

I edited your code and did the same with the Extract B2B sheet and got the headings correct. But I am not getting the formulas.
Maybe this line needs editing. I was not able to edit as I didn't understand that line.

Please Login or Register  to view this content.

Originally Posted by bebo021999

1) With your current process:
try below code:

PHP Code: 

Option Explicit

Sub GetFormulas()
Dim FormulasArray()         As Variant
Dim LastRowKAB2B            As Long
Dim i&
LastRowKAB2B = 5925 ' last row
ReDim FormulasArray(1 To 5)
'=VLOOKUP(Z5,'Extract B2B'!$Q$2:$Q,1,0)
    FormulasArray(1) = "=VLOOKUP($Z5,'Extract B2B'!$Q$2:$Q" & LastRowKAB2B & ",1,0)"                                        ' Formula for column D of 'Extract B2B'
'=SUMIF('Extract B2B'!$Q$2:$Q$9329,W5,'Extract B2B'!$I$2:$I$9329)/COUNTIF('Extract B2B'!$Q$2:$Q$9329,KA!W5)
    FormulasArray(2) = "=SUMIFS('Extract B2B'!$Q:$Q,$W5,'Extract B2B'!$I:$I)/COUNTIF('Extract B2B'!$Q:$Q,$W5)"         ' Formula for column X
'=X5-E5
    FormulasArray(3) = "=$X5-$E5" ' Formula for column Y
'to correct =A5&" | "&C5
    FormulasArray(4) = "=$A5& "" | "" & $C5"  ' Formula for column Z
'=IFERROR(IF(AND(B2>=-1,Y5<=1),"Matched",""),"Not Appearing")
    FormulasArray(5) = "=IFERROR(IF(AND($B2>=-1,$Y5<=1),""Matched"",""""),""Not Appearing"")"                                       ' Formula for column C of 'Extract B2B'
'to correct
For i = 1 To 5
   Sheets("KA").Cells(5, 22 + i).Value = FormulasArray(i)
Next
End Sub 


2) I dont know for sure what are you trying to do, but I am for sure that there are proper way to archive the results, instead of trying to create header combination to get columns combination
If you are willing to upgrade it, try to remove all helper columns/ header, then manual input what is the expected outcome, then upload file again.

You were right bebo. I need to enter the headings manually each time and then run the code. After spending the whole day, I kept getting errors and was not able to solve the issue. Maybe tomorrow hopefully I will try from the beginning once again and get the correct result.
Thanks Bebo.

beyond excel. Excellent code. Wish I could understand the code as it would help me to edit whenever and where ever necessary. Actually, as I am not a so expert coder, I create new applications referring the comments.
Thank you very much for your time and especially the code. Now I have some extra work to do that is understand the code.
Thanks once again beyond excel.