Solved: vba to insert formula using variable

[kevvukeka]

Hi all, I have a master data on which I need to use specific formulae and ilter the data. Now I have stored these formulae in range a2:a13.

I am trying to loop through those formulae and assign to a cell. but the I don't see formula getting inserted in the cell. whats wrong the below line. can some on help..


For Each Fcl1 In rFrng
With wsh1
.Cells(2, icolcount + 1).Formula = " = " & Fcl1
End With
Next Fcl1



Thanks in advance

[xls]

hi

formula will be inserted for 1st row after that you have to move to next cell which is not done by For ..Next loop.

You can use offset to move to next cell

HTH

[mancubus]

perhaps...


For Each Fcl1 In rFrng
With wsh1
.Cells(2, icolcount + 1).Formula = "=" & Fcl1
End With
icolcount = icolcount + 1
Next Fcl1

[xls]

True

[kevvukeka]

Hi All,

I tried your suggestions . but I don't see the formula getting inserted in the cell. I would like to show the entire thing in details

The formula in sheet2. range "A2" is
"IF(AK2=""OTC"",IF(BA2=0,"""",""Incorrectly OTC Paid | "" & J2 & ""|"" & CB2),"""")"

I am using for each loop to loop through all the formula in the range.

so to get the first formula in cell cc2 I used the below code:



icolcount = wsh1.Cells(1, Columns.Count).End(xlToLeft).Column





With wsh1


For Each rForcel1 In rForrng1

.Cells(2, icolcount + 1).Formula = "=" &
rForcel1

Next
rForcel1
End With


I don't see the formula in cell cc2. if I include "=" in the formula itself I get the result as = =IF(AK2=""OTC"",IF(BA2=0,"""",""Incorrectly OTC Paid | "" & J2 & ""|"" & CB2),"""")"

Please suggest.

[mancubus]

apart from the problem, that bit of your code repeatedly writes the values of cells in rForrng1 to second cell of first blank column.


i still dont understand the problem and now try to guess what it might be:

after the code is run;
1- cell is still blank,
2- i see the formula instead of formula result,
3- the formula is written as text.

which one? or another one maybe?


can you post your workbook with fake data? and show us before and after situations of spreadsheet?

[kevvukeka]

Hi All,

I was able to able to fix the error. The formulas which I stored in a range where actually containing extra quotes. removing them helped me achieve my requirement. Thanks for your help..