Convert formulas to vales

[Aland2929]

A user select a product and this product is added to the next empty row and columns to the right are filled in using vlookup formulas in VBA. There are some blank columns between the formulas. I want to convert the formulas to values before the next product is selected but my code is not working? Columns B to H has data, column I is empty and columns J & K have data. Would really appreciate any assistance.
Thanks in advance

Please Login or Register  to view this content.

[sintek]

Hi Aland2929
Will be easier if you can upload a sample workbook so that we can see all the code.
Perhaps:

Please Login or Register  to view this content.

[Aland2929]

Thank you for your reply - all code before the copy and paste is working fine (I am using vlookup formulas based on product chosen and if I don't convert formulas to values, all the rows have the same vlookup answers) - my problem is the copy paste code - I want to convert the row I have posted to values but there is a blank column between the formulas so End(xlToRight) does not include all columns.
Thanks for your help.

[jindon]

Are you sure

Please Login or Register  to view this content.

is working?

[Aland2929]

Thanks for your reply - I have attached a sample file which illustrates the problem.

[jindon]

Is this just a demonstration purpose?

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Aland2929]

Thanks for your reply - In the real workbook, I only want to convert formulas to values from column B to column G. In columns H, j & K, the formulas must remain - the formulas below are for columns H, j & K but they are not relative? Will really appreciate your assistance.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Or?

Please Login or Register  to view this content.

[Aland2929]

Thank you very much (works perfectly) - I still have a problem with the following formulas as they always refer to row 14?

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

[Aland2929]

Thank you for your reply - I need the formulas to remain in columns H, J & K as they respond to user input in column I. The problem I have is the formulas always refer to row 14?

[jindon]

Hey, you are starting from ActiveCell, so it can be any column. How would somebody else know the column from ActiveCell.Offset ?

[Aland2929]

The formulas are being posted to the next empty row correctly but the formulas in columns H, J & K always refer to row 14 as if they are absolute but they need to be relative?

[jindon]

If the columns in formula are fixed

Please Login or Register  to view this content.

[Aland2929]

Thank you - formulas are now relative.