Search code examples
vbaexcelformulas

VBA userform formula

Good afternoon,

I have built a userform and the userform populates the data on to the data table using offsets etc. The problem I have is one of the formulas is looking up the description of the product from a list on a separate tab, but it is deleting the zeroes contained within the product code rendering the formula useless for example:

The formula

ActiveCell.Offset(x, 17) = "=If(LEFT(" & ActiveCell.Offset(x, 16) & ",1)=""Z"",VLOOKUP(" & ActiveCell.Offset(x, 16) & ",Masterdata!A:B,2,0),VLOOKUP(TEXT(" & ActiveCell.Offset(x, 16) & ",0),Masterdata!A:B,2,0))"

Product code it is looking up is Z6532018-11-002-17

After the VBA has run, the formula in the cell looks like this:

=IF(LEFT(Z6532018-11-2-17,1)="Z",VLOOKUP(Z6532018-11-2-17,Masterdata!A:B,2,0),VLOOKUP(TEXT(Z6532018-11-2-17,0),Masterdata!A:B,2,0))

It has automatcially deleted the 00 from 002-17 within the product code.

Any ideas on how to stop this?

Many thanks

Solution

  • try using the cell address instead of putting the reference: 

    ActiveCell.Offset(x, 16).Address(0,0)

    If you do not wnt that then you will need to add " around the lookup. 

    """ & ActiveCell.Offset(x, 16) & """

    So it returns: "Z6532018-11-002-17" not just the text. The text needs to have a " on either end in the finished formula.