Hi I have a Table in Sheet Item.
The table range may start from any cell in the sheet.
Hence i need a code to perform below task.
First find non blank cell in sheet "Item"
Copy the column till the last row, go to last column and +1 and paste.
Give 2 headings "% of claims" and "% of amount"
Calculate some formula in next columns.
I have given the required result in Sheet along with required formula "Result after code"
Please help
Try this
Please Login or Register to view this content.
Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
Just when I think I am smart, I learn something new!
Hi ! Try this !
According to your attachment as a starter :
PHP Code:Sub Demo1()
With Sheets("items").UsedRange.Find("Amount", , xlValues, xlWhole, xlByRows).CurrentRegion.Columns
Union(.Item(1), .Item(3)).Copy .Range("E1")
.Range("A1:B1").Copy .Range("G1")
.Range("G1:H1").Value2 = [{"% of Claims","% of amount"}]
With .Item("G:H").Rows("2:" & .Rows.Count)
.Borders.Weight = xlThin
.NumberFormat = "0.00%_w"
.Formula = "=" & .Cells(1, -4).Address(False, False) & "/" & .Cells(.Count, -4).Address(, False)
.Formula = .Value2
.Item(.Count).Font.Bold = True
End With
.Item("E:H").AutoFit
End With
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 08-15-2019 at 07:32 PM. Reason: optimization …
Hi,
Here range is defined in a code.
I my data the data starting cell will differ every time.
Hi,
it is not copying Item and No. of Claims.
Required result is same as in sheet "Result after code"
Originally Posted by saravanan1981
Nope in both codes ‼ At least try before …
As my demonstration returns exacly the expected result, again just try before !
Your example did not show number of claims being copied, only columns 1 and 3 of origiinal table was copied.
Last edited by JLGWhiz; 08-16-2019 at 12:31 PM.
See if this is more to your liking.
Please Login or Register to view this content.
Hi JLGWhiz,
Its Simply Superb.
I just need one more help on the same code.
While copying to new column, do not copy "No. of Claims" (I know I have asked for it earlier. But now no need). Also heading format of % Claims and % Amount
to be same as other hadings. Also the multiplication formula should be vissible instead of values.
Simply I want the result same as "Result after code".
According to your attachment you already had it since post #3 ‼
If you are going to use formulas, then there is not much point in using VBA. The code provided does the same task as the formulas and leaves the Sheet unincumbered by the formulas, thereby reducing the probability that someone will inadvertantly change or delete the formulas through editing.
Here is all execept the formulas.
Please Login or Register to view this content.
Hi JLGWhiz,
Thanks a ton for your help.
You are welcome,
Regards, JLG
Hi JLGWhiz,
Just wanted to check, It that possible to add one more column, as mentioned in Column K. if the amount is less than lakh, amount should be converted into numbers as mentioned in K11.
If the amout is grater than lakhs, it should convert in 2 decimal like as mentioned in K12. If it is more than 1 crore than mount should converted to 2 decimal as mentioned in K7.
In such way i wanted to make a sentence in column K.
I dont know whether it is possible or nor. Please advise.
Last edited by saravanan1981; 08-18-2019 at 09:55 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks