Problem with loop coding and formatting providing a #N/A error

Hi

I am fairly new to producing macros so if this doesn't make sense, I apologise.

What I am trying to do is to produce shipping costs from a calculator that's based in another workbook. The problems that I am having are as follows:
the calculator isn't working unless I manually type in the weight, if I copy and paste from the workbook into the calculator it returns a #n/a result
I can't seem to get the code right to be able to work down a list of titles until I reach an empty cell.

Any advice on either of the above 2 issues would be greatly appreciated as this is keeping me up at night now!!

Thanks in advance

Please Login or Register  to view this content.

Hi, Sue991,

Iīm not sure the calculator will accept the macro but itīs a bit shorter (and avoids a lot of changing between workbooks). Please use a copy of the original workbook for testing the code which should go into a standard module:

Please Login or Register  to view this content.

This code is untested - there may be occur run time errors (letīs hope they wonīt).

Ciao,
Holger

Hi Holger

Thank you so much, you are an absolute star! The macro appears to run and the fields are completed however I've still got the formatting issue with taking the weight figure from the worksheet and inputting it into the calculator. Any ideas?

Thanks again
Sue

Hi, Sue,

depending on what you need to enter thereīs the Format-Function which could help. Short example:

Please Login or Register  to view this content.

This will show a text which needs to be converted to a number (i.e. by adding 0). Same could be achieved by using Text instead of Value when transferring the data.

Sorry, I know it sounds confused but could you be a bit more precise on whatīs exactly in the cell to copy and whatīs expected to be inserted into the calculator?

Caio,
Holger

Hi Holger
The calculator that I've produced works on full numbers only however the cell that I want to take the figure from is a calculation of a print run x book weight + 6% and therefore the result is not a full number. For example print run = 200 x book weight = 0.76kgs = 153KGS + 6% = 161.12. I need to round up or down the number in order to use the calculator.
Thanks again, your help is really appreciated.
Sue

Hi, Sue,

you could utilize WorksheetFunction.Round(Cell.Value, 0) for that.

An example for the first line of and value of a cell from the code

Please Login or Register  to view this content.

Or you might like to use RoundUp, RoundDown, or use Fix or Int. There are far more ways than I mentioned.

Please give it a try.

HTH
Holger

Hi Holger
Within the original code that you gave me, where should I add this line? Sorry, but I am a real novice at this
Sue

Hi, Sue,

no problem - seems I havenīt been very clear on that one cause (should activate the line number for the code for clarification maybe). Assuming that itīs for all the values which are pasted into the calculator:

Please Login or Register  to view this content.

The original lines have been commented out - they should appear in green colour in th eeditor (by default at least).

HTH
Holger

Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [ code ] Your code here [ /code ] (without spaces in the brackets)

Hi, Sue,

whatīs the cell type in Column P? I assumed it to be numeric and thus added the Round-Function.

Please point out if the rounding must be applied for all three cells to be carried over.

Ciao,
Holger