VBA array make value from FormulaR1C1

[roykana]

Dear all master,
Please help me to code vba array so the result is not formula.

Set rng1 = TargetSheet.Range("G2")
     Range("G1").Value = "REMARK"
     Range("E1").Value = "D/C"
     Range("H1").Value = "REMARKHELP"
     Range("I1").Value = "REMARKFIX"
With TargetSheet
     rng1.FormulaR1C1 = _
        ""
      rng1.FormulaR1C1.Value = rng1.FormulaR1C1.Value
End With
     Set rng3 = TargetSheet.Range("H2")
     With TargetSheet
     rng3.FormulaR1C1 = _
        ""
      rng3.FormulaR1C1.Value = rng3.FormulaR1C1.Value
End With
Set rng4 = TargetSheet.Range("I2")
     With TargetSheet
     rng4.FormulaR1C1 = _
        ""
      rng4.FormulaR1C1.Value = rng4.FormulaR1C1.Value
End With

Thanks
Roykana

 

[jasonb75]

Perhaps a better example of what you are trying to do might be useful. There are no arrays in your code and there is nothing that resembles a formula so it is very unclear what is actually required.

 

[roykana]

Perhaps a better example of what you are trying to do might be useful. There are no arrays in your code and there is nothing that resembles a formula so it is very unclear what is actually required.

Dear Mr jasonb75

hereby, according to your request, I give the full vba code test and attach the test excel file
LINK

VBA array make value from FormulaR1C1.xlsm

drive.google.com

I want to make a vba array code whose result is a value not a formula.
Thanks
roykana

Option Explicit
Sub Test()

    'By Kana @ 919328093207 [E] kana250688@gmail.com
    Application.ScreenUpdating = False
    Dim TargetSheet As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
    Set TargetSheet = Sheets("BCA")
    Application.ScreenUpdating = False
    
     
     Range("G1").Value = "REMARK"
     Range("E1").Value = "D/C"
     Range("H1").Value = "REMARKHELP"
     Range("I1").Value = "REMARKFIX"
     Set rng1 = TargetSheet.Range("G2")
With TargetSheet
     rng1.FormulaR1C1 = _
        "=IF(LEFT([@Keterangan],15)=""KR OTOMATIS LLG"",TRIM(RIGHT([@Keterangan],LEN([@Keterangan])-FIND(""~"",(SUBSTITUTE([@Keterangan],""  "",""~"",1))))),IF(ISNUMBER(SEARCH(""/FTFVA/"",[@Keterangan])),SUBSTITUTE(TRIM(RIGHT([@Keterangan],SEARCH(""/FTFVA/"",[@Keterangan])+1)),""/"",""""),IF(ISNUMBER(SEARCH(""TARIKAN ATM"",[@Keterangan])),""TARIKAN ATM"",IF(LEFT([@Keterangan],9)=""SWITCHING"",TRIM(RIGHT([@Keterangan],LEN([@Keterangan])-FIND(""~"",(SUBSTITUTE([@Keterangan],""  "",""~"",1))))),SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE([@Keterangan],""  "",REPT("" "",255)),255)),""."","""")))))"
'      rng1.FormulaR1C1.Value = rng1.FormulaR1C1.Value
End With
     Set rng3 = TargetSheet.Range("H2")
     With TargetSheet
     rng3.FormulaR1C1 = _
        "=LEFT(IF(LEFT([@Keterangan],15)=""KR OTOMATIS LLG"",TRIM(RIGHT([@Keterangan],LEN([@Keterangan])-FIND(""~"",(SUBSTITUTE([@Keterangan],""  "",""~"",1)))))),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},IF(LEFT([@Keterangan],15)=""KR OTOMATIS LLG"",TRIM(RIGHT([@Keterangan],LEN([@Keterangan])-FIND(""~"",(SUBSTITUTE([@Keterangan],""  "",""~"",1))))))&""0123456789""))-1)"
'      rng3.FormulaR1C1.Value = rng3.FormulaR1C1.Value
End With
Set rng4 = TargetSheet.Range("I2")
     With TargetSheet
     rng4.FormulaR1C1 = _
        "=IF([@REMARKHELP]=""FALSE"",[@REMARK],[@REMARKHELP])"
'      rng4.FormulaR1C1.Value = rng4.FormulaR1C1.Value
End With
    MsgBox "Process Done", vbInformation, "Message from Kana"
    
    Application.ScreenUpdating = True
End Sub

 

[jasonb75]

Either use what you already have but correct the .Value lines.

rng4.Value = rng4.Value

Or evaluate the formula directly to the cell.

rng4.Value = _
       Evaluate("=IF([@REMARKHELP]=""FALSE"",[@REMARK],[@REMARKHELP])")

I've used rng4 in the examples as it had the shortest formula but the same method applies to all of them.

 

[roykana]

Either use what you already have but correct the .Value lines.

rng4.Value = rng4.Value

Or evaluate the formula directly to the cell.

rng4.Value = _
       Evaluate("=IF([@REMARKHELP]=""FALSE"",[@REMARK],[@REMARKHELP])")

I've used rng4 in the examples as it had the shortest formula but the same method applies to all of them.

Dear Mr. jasonb75,
thank you for your answer.

rng4.Value = rng4.Value

If I use the first solution then the value is only in CELL I2

rng4.Value = _
       Evaluate("=IF([@REMARKHELP]=""FALSE"",[@REMARK],[@REMARKHELP])")

If I use the second solution then it appears on CELL I2 "#VALUE!"
Thanks
roykana

 

[jasonb75]

If I use the second solution then it appears on CELL I2 "#VALUE!"

It is possible that the method suggested does not work with table names, only regular ranges. With that in mind it would be easier and safer to use the first method.

If I use the first solution then the value is only in CELL I2

As I said, I used rng4 as an example, you would need to apply the same to rng1, rng2, etc.

 

[roykana]

It is possible that the method suggested does not work with table names, only regular ranges. With that in mind it would be easier and safer to use the first method.

As I said, I used rng4 as an example, you would need to apply the same to rng1, rng2, etc.

I use the first solution in rng4 results only in cell I2

Thanks
roykana

 

[roykana]

I use the first solution in rng4 results only in cell I2

Thanks
roykana

How do I use the first solution in rng4 the result is from I2 to the end of the data?

 

[jasonb75]

from I2 to the end of the data?

There was no mention of going to the end of the data, my suggestions were based on your code which was using a single cell. Remember that most of us are unable to download attached workbooks. Without a visual representation that we can see it is often easy to overlook a small detail that you might think is obvious.

If your table is named "Table1" then you would use.

Set rng4 = TargetSheet.Range("Table1[REMARKFIX]")

You don't need to use 'With TargetSheet' in your code, when the range is 'Set' is is locked to the correct sheet.
However, you will need to add references to 'TargetSheet' to the lines that fill in E1 and G1:I1 otherwise they will be added to the wrong sheet if TargetSheet is not currently selected.

 

[roykana]

You don't need to use 'With TargetSheet' in your code, when the range is 'Set' is is locked to the correct sheet.
However, you will need to add references to 'TargetSheet' to the lines that fill in E1 and G1:I1 otherwise they will be added to the wrong sheet if TargetSheet is not currently selected.

Thank you for your reply and it's appropriate.
you mean i don't need to use "with targetsheet" then please provide solution and example.

Thanks

roykana