Need to convert one formula into code and add it to the existing code

[RAJESH SHAH]

Hello code experts
I need your help to add at the end of this code a formula which is to be converted into a code to get the final result. When I run the code, a new sheet is created with the Name of "GSTIN Verified.". I want to include in the code, to add this formula to get the combined values of J K L M and N in column P with the same code.
=TRIM(SUBSTITUTE(J2&" "&K2&" "&L2&" "&M2&" "&N2,"0",""))
If you convert the formula and get it in the second row below the heading, then resize the column P with column B and fill down.
To test the code for the second time, delete the GSTIN Verified sheet and then run the code.
Expected result sheet attached.

[rollis13]

This would be the example to fill your second cell of column P; now all you have to do is adapt/add it to your project:

Please Login or Register  to view this content.

[RAJESH SHAH]

rollis13. The code hasn't entered the formula in column P2. It is blank like before Even after entering the code at the end of the previous code.

I entered the above and got the formula in P2. Will try to resize and fill down. If I am not able to resize then I will let you know.
Thanks rollis13.

[RAJESH SHAH]

Referring the old codes for resizing the rows, they are connected to 2 sheets and resizing the whole row. I am unable to edit toresize it and filldown one row only.
Will try again tomorrow. Time to hit the sack.

[RAJESH SHAH]

The resize of column P worked. I was able to fill down the rows from P2 to the last row. Thanks for the formula converted into code rollis13.
code to resize is

[rollis13]

Glad having been of some help. It would be nice to see your resize code (it's missing in post #3 and #5).

[RAJESH SHAH]

Option Explicit

Sub ResizeRows()
Dim x As Long, y As Long
Sheets("GSTIN Verified").Activate
y = Sheets("GSTIN Verified").Range("P2").CurrentRegion.Columns.Count
x = Sheets("GSTIN Verified").Range("A2:A" & Sheets("GSTIN Verified").Range("A" & Rows.Count).End(xlUp).Row).Rows.Count
Range("P2").Resize(x, y).FillDown

End Sub

[RAJESH SHAH]

I added your code of converting the formula at the end of the sheet.
WrapUp:
Debug.Print "Time elapsed = " & (MicroTimer - StartTime) & " seconds." ' Display Elapsed Time into Immediate Window (CTRL+G)
MsgBox "GSTIN verification Completed." ' Notify user that the script has finished
'code by rollis13
Sheets("GSTIN Verified").Select
'=TRIM(SUBSTITUTE(J2&" "&K2&" "&L2&" "&M2&" "&N2,"0",""))
Range("P2").Formula = "=TRIM(SUBSTITUTE(RC[-6]&"" ""&RC[-5]&"" ""&RC[-4]&"" ""&RC[-3]&"" ""&RC[-2],""0"",""""))"

Call ResizeRows.ResizeRows


End Sub

[rollis13]

Thanks for the feedback .
Tip: to obtain a R1C1 version of a formula (well that's how I do it ). Go to menu File / Options / Formulas / section Working with formulas / and check "R1C1 reference style", copy your formula and paste it into your macro, then reverse Options.
Once pasted in your code all you have to do is add double-quotation around every double-quotes found in the formula. Example: " " becomes "" "", "0" -> ""0"", "" -> """".

[AliGW]

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[RAJESH SHAH]

I did as you said to check the box RICI but the remaining part I didn't understand. I hope it doesn't effect the formula.

[RAJESH SHAH]

I thought it was the HTML button tags around selected text. I was also wondering why was it not displaying. So, I should add just # and paste the code like this is it ?
#Range("P2").Formula = "=TRIM(SUBSTITUTE(RC[-6]&"" ""&RC[-5]&"" ""&RC[-4]&"" ""&RC[-3]&"" ""&RC[-2],""0"",""""))"

[rollis13]

Post #11:
Let's say that you have a working formula in your cell and you would like to used it in your macro. Change your Options, go to the cell, copy the formula and paste it in your macro then remember to reverse your option. In the formula in your macro, as said, add double-quotes where needed.

Post #12:
You need to click the # you see in the Icon Bar and in the message-box will appear the tags [CODE ][/CODE ]. The code of your macro goes in between the tags.

[RAJESH SHAH]

Thanks rolllis13. That was a simple way of explaining.
I understood and got the reply for post #12.
Post #11 , I will have to check and practice manually later. Right Now I have a lot of pending queries to solve.
"Code to create a sheet and get selected data from the source sheet" is one of them. Can you help me with this ? I am not getting any replies here nor in the other forum where I posted.

[rollis13]

[...] I am not getting any replies here nor in the other forum where I posted.

That's probably because people don't like cross-posting, or, maybe you didn't explain well your request.