Posts by Excel&VBA

Yes you are right.

Thank you very much royUK it is working great. Thank you for the clarification as well.

Thank you so much for the great help. Exactly this clarifies.

The problem is that code should paste the resul till Col"H" used range that is Row 228. But code paste the result till where Table ends that is row 500.

Yes it is right.

Carim Thank you so much i did it with the help of macro recorder.

Just last thing that how to ignore

LastRowColumnA = Cells(Rows.Count, 8).End(xlUp).Row 

Excel Table blank cells because Col8 used range ends on 200 row but due to table it paste the result till table last range that is 500.

Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 8).End(xlUp).Row
Sheet1.Range("A2:A" & LastRowColumnA).Formula = "=IF(ISERROR(VALUE(SUBSTITUTE(LEFT(RC[7],FIND("" "",RC[7])-1),""_"",""""))),R[-1]C,LEFT(RC[7],FIND("" "",RC[7])-1))"
Sheet1.Columns(1).Copy
Sheet1.Columns(1).PasteSpecial xlPasteValues

I have this below formula and trying to convert it in VBA

=IF(ISERROR(VALUE(SUBSTITUTE(LEFT(H2,FIND(" ",H2)-1),"_",""))),A1,LEFT(H2,FIND(" ",H2)-1))

When i add this formula into below code it gives me red highlighted text for whole formula.

I just want that when i run this code it should paste the values instead of formula into cells. OR if this formula can be converted into code then please convert this.

Looking forward to your help.

Sub FormulaFill()
Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 8).End(xlUp).Row
Sheet16.Range("A2:A" & LastRowColumnA).Formula = "Above Formula"
End Sub

Thank you sooo much Carim FOR THIS GREAT HELP.

just for this

00202_0000

Yeah i know my code is not much advance but it have to change.

Exactly you are right now i just want to make my code to work fast Because it is very slow it takes 5 and more minutes to perform this calculations.

and i have attached a sheet where it will paste and transpose in the sequence as i mentioned manually in the Sheet PasteData.

Now there is just to things that i want to add in my code.

Paste and Transpose sequence as available in the attached workbook and code speed issue.

Thank you.

Yes i want to perform all the calculations till the given last year in in sheet data.

the code i have created i working according to my needs but it is very very slow id o not know why.

Thank you so much for replying.

I have tried to created a code according to my requirements.

The Code have all answers to your recent questions. Please have a look on attached sheet and its code.

I can share more details of required. Carim

I have create separate worbooks for the result and Code i am looking for.

When code will run It will take Col"B" first value [Blocked Image: https://www.ozgrid.com/forum/c…asted-from-clipboard-png/] and paste Row 7, 8 and 9 into Sheet Pastedata as i pasted in attached Book1 and after that whatever value is appear in Sheet("Calc").Range("AE10:AG10") that will be copied and paste into Sheet("Final").Range("E2:G2").

Now Code did first JOB and will go for second.

Code will pick 2nd number [Blocked Image: https://www.ozgrid.com/forum/c…asted-from-clipboard-png/] and paste row 13, 14 and 15 into Sheet Pastedata as i pasted in attached Book2 and after that whatever value is appear in Sheet("Calc").Range("AE10:AG10") that will be copied and paste into Sheet("Final").Range("E3:G3").

Now Code did 2nd JOB and will go for third.

Code will pick 3rd number 00204_0000 and paste row 20, 21 and 22 into Sheet Pastedata as i pasted in attached Book3 and after that whatever value is appear in Sheet("Calc").Range("AE10:AG10") that will be copied and paste into Sheet("Final").Range("E4:G4").

I want that final result. from that code please have a look Carim,

I know this is hectic. I would really appreciate your help.

I do not know how to elaborate it.

Let me try again.

We do not need to use SUMIF to SUM the whole data, we will pick ColB first Number number that is and will match in Col"E"

and that number is just available in circled cells so row 7,8 and 9 data will be pasted into Pastedata Sheet.

after that whatever the result is appear in Sheet Calc that result will be paste into Sheet final.

then code will go for 2nd Number now same process will be repeat for 2nd number now code will pick ColB 2nd Number from ColB because first number has been completed.

then go for number 3 and so on

A loop will create to repeat the process i think.

Let me share an example for the first number that is "00202_0000".

Same activity would be done for remaining numbers Available in Sheet("Data") ColB

NO, NO I think i could not elaborate that what i want from the new given sheet, Please Do not delete the other tabs let me explain you briefly that what i want.

The previous code just pasting the data of "Gorss Wage".

Now the code will paste the data of three rows name are added below (that will be Sheet("Data") row 7,8 and 9.

THE CODE WILL NOT USE SUM/SUMIF IT WILL AS IT PASTE THE ROWS.

00202_0000 - Gross Wage

00202_0000 - Gross Lable

00202_0000 - Gross DD

Code will take the first number from Col"B" and will match that number in Col"E" if that number matches then code will paste that matches row data into Sheet("Pastedata") as per same sequence that previous code was doing BUT WILL NOT sum/sumif.

Now there is one more step further that is whatever the result is appear in Sheet("Calc").Range("AE10:AG10") after pasting the data into Sheet("Pastedata") for the first *number* that is "00202_0000".

That metnioned Sheet("Calc").Range("AE10:AG10") will be copied and paste into Sheet("Final").Range("E2:G2")

after that Code will go for 2nd number 00203_0000 then for 3rd 00204_0000 an so on.

If the the CODE has completed the calculation for 1st number. Then same activity would repeat for 2nd number then 3rd and 4th an so on.

THIS IS EXACTLY WHAT I WANT FROM THE NEW SHEET.

Alright i admitted it. Thanks

Your previous code helped me a lot for my working thank you so much.

I need some more favor if you are willing to provide i will really appreciate the help.

The previous code just pasting the data of "Gorss Wage".

Now the code will paste the data of three rows name are added below (that will be Sheet("Data") row 7,8 and 9.

00202_0000 - Gross Wage

00202_0000 - Gross Lable

00202_0000 - Gross DD

Code will take the first number from Col"B" and will match that number in Col"E" if that number matches then code will paste that matches row data into Sheet("Pastedata") as per same sequence that previous code was doing.