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]

you mean i don't need to use "with targetsheet" then please provide solution and example.

Just delete the lines that say 'With ....' or 'End With'

To use the With method properly, it would be something like this. (Table names might need changing).

Option Explicit
Sub Test()
Application.ScreenUpdating = False

Dim TargetSheet As Worksheet
Set TargetSheet = Sheets("BCA")

With TargetSheet
    .Range("G1").Value = "REMARK"
    .Range("E1").Value = "D/C"
    .Range("H1").Value = "REMARKHELP"
    .Range("I1").Value = "REMARKFIX"
    
    With .Range("Table1[REMARK]")
        .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)),""."","""")))))"
        .Value = .Value
    End With
    
    With .Range("Table1[REMARKHELP]")
        .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)"
        .Value = .Value
    End With
    
    With .Range("Table1[REMARKFIX]")
        .FormulaR1C1 = "=IF([@REMARKHELP]=""FALSE"",[@REMARK],[@REMARKHELP])"
        .Value = .Value
    End With
    
MsgBox "Process Done", vbInformation, "Message from Kana"
    
Application.ScreenUpdating = True
End Sub

 

[roykana]

sorry I'm late to reply. thanks for your reply

Option Explicit
Sub Test()
Application.ScreenUpdating = False

Dim TargetSheet As Worksheet
Set TargetSheet = Sheets("BCA")

With TargetSheet
    .Range("G1").Value = "REMARK"
    .Range("E1").Value = "D/C"
    .Range("H1").Value = "REMARKHELP"
    .Range("I1").Value = "REMARKFIX"

    With .Range("Table1[REMARK]")
        .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)),""."","""")))))"
        .Value = .Value
    End With
    
    With .Range("Table1[REMARKHELP]")
        .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)"
        .Value = .Value
    End With
    
    With .Range("Table1[REMARKFIX]")
        .FormulaR1C1 = "=IF([@REMARKHELP]=""FALSE"",[@REMARK],[@REMARKHELP])"
        .Value = .Value
    End With
End With
MsgBox "Process Done", vbInformation, "Message from Kana"
    
Application.ScreenUpdating = True

End Sub

in the code you provided there is a missing "end with" and
I have added
n the code that you provided into a value there is a difference in the results so I have to change it from ""FALSE"" to FALSE.

  With .Range("Table1[REMARKFIX]")
        .FormulaR1C1 = "=IF([@REMARKHELP]=FALSE,[@REMARK],[@REMARKHELP])"
        .Value = .Value
    End With

 

[jasonb75]

I have to change it from ""FALSE"" to FALSE

I thought that it was unusual but I took the formulas from your original code and left them as they were.

With the changes that you have made, is it working as needed now?

 

[roykana]

I thought that it was unusual but I took the formulas from your original code and left them as they were.

With the changes that you have made, is it working as needed now?

sorry I replied late and yes it went perfectly

 

[roykana]

sorry I replied late and yes it went perfectly

Thank you for your help and ok I marked the solution for you