PLease i need help

[AbdulkareemAlhassni]

Hello,

I have been having trouble with this code for more than a week, i have tried to find a solution but i could not

'THE BELOW IS MY DESIRED MAGIC
LastRow = Range("A1").End(xlDown).Row


Range("A1").Activate
n = ActiveCell.EntireRow.Find("Requisition", LookAt:=xlWhole).Column
Cells(1, n).Select
ActiveCell.Offset(1, 0).Select


'With Range(ActiveCell, ActiveCell.Offset(numRows, numCols))
With Range(ActiveCell, ActiveCell.Offset(numRows, numCols))
    .FormulaArray = "=IF(A1="""","""",A1)"
    .Replace "A1", "INDEX('PO Dist'!$W$2:'PO Dist'!$W$100000,MATCH(1,(A2='PO Dist'!$A$2:$A$100000)*(B2='PO Dist'!$B$2:$B$100000),0))"
End With


ActiveCell.AutoFill Range(ActiveCell.Address, Cells(LastRow, ActiveCell.Column))

The problem is the Code keeps only inputting A1 value in the excel sheet.

THE REPLACE DOES NOT WORK, I DONT KNOW WHY

 

[jorismoerings]

Hi,

Not fond of getting private messages saying "

hey man can you please help !"
I'm supporting the forum if and when I am able to help.
So please stop using private messages.

Now your problem: because the VBA you're showing doesn't make much sense, the principle question is: what are you trying to achieve?​

Your not giving me enough information to be able to help you.
Take a close look at the forum rules and guidance , follow them and the chance of getting support on your question is increased by numerous times.

 

[JLGWhiz]

.Replace "A1", "INDEX('PO Dist'!$W$2:'PO Dist'!$W$100000,MATCH(1,(A2='PO Dist'!$A$2:$A$100000)*(B2='PO Dist'!$B$2:$B$100000),0))"

the first reason the Replace function did not work is that the syntax for your worksheet formula is not correct. You cannot just plug a worksheet fromula into vba in the same format as it is used on a worksheet. I don't even want to try and recode the statement. I agree with @jorismoerings that you need to give a better explanation of what you are trying to do.

 

[StephenCrump]

Here's some context: https://www.mrexcel.com/forum/excel-questions/1107091-vba-match-array-error-1004-a.html

Your .FormulaArray string is now <255 characters, so we don't need to use the .Replace construction.

Because you want an array formula in each cell, you need to enter the formula into a single cell and use Copy or Autofill to populate the other cells.

If instead you set the .FormulaArray property of a multi-cell range, you'll end up with a single array formula across the cells. Compare:

'Individual array formula in each cell - this is what you want
With Range("C10")   'say
    .FormulaArray = "=IF(INDEX('PO Dist'!$W$2:$W$100000,MATCH(1,(A2='PO Dist'!$A$2:$A$100000)*(B2='PO Dist'!$B$2:$B$100000),0))="""","""",INDEX('PO Dist'!$W$2:$W$100000,MATCH(1,(A2='PO Dist'!$A$2:$A$100000)*(B2='PO Dist'!$B$2:$B$100000),0)))"
    .Copy Range("C11:C15")   'or you can use autofill
End With


'One array formulae across the five cells - you don't want this!
Range("C10:C15").FormulaArray = "=IF(INDEX('PO Dist'!$W$2:$W$100000,MATCH(1,(A2='PO Dist'!$A$2:$A$100000)*(B2='PO Dist'!$B$2:$B$100000),0))="""","""",INDEX('PO Dist'!$W$2:$W$100000,MATCH(1,(A2='PO Dist'!$A$2:$A$100000)*(B2='PO Dist'!$B$2:$B$100000),0)))"