Posts by cjh1984

Thank You!! You are a star! That works great!

How are your INDEX and MATCH skills?? I am having a dilema with that and once that is complete my project is finished!!

Thank you again!

I can see the problem now. You are copying the cells and then deleting the Sheet they are on. You have to copy them, insert a new sheet, paste and then delete the other Sheet.

That should work

Try inserting:

Cells.Select

before the line where the error occurred.

Let me know if that works or not.

To stop the pop-up appearing place:

Application.DisplayAlerts = False

at the beginning of your code.

What is the text which comes with the error?

Copy all of your code here.

Ok, in your example you have {.....} at the beginning and end of the formula, wen i dont have those in it doesnt work but when i put it back in it doesnt calculate the formula it just shows it as text in the cell.

Why is this?

Thanks, I'm guessing that the number 11 represents the number of characters?

What do the 0 and the 1 represent which follow this at the end of the formula?

Ok, on the spreadsheet you sent me in Sheet1 I changed 123457 to OP02.123457 because I forgot to mention that all values start with OP02. or a different combination of 4 characters followed by a full stop and the 6 numbers.

I changed the value in Sheet2 to OP02.123457.aaa but when I returned to Sheet1 the Sheet2 Value column showed #N/A.

What do I need to change in the formula {=INDEX(Sheet2!A1:E10,MATCH(A4,LEFT(Sheet2!A1:A10,6),0),1)} so that it works.

Thank You

To close it without having to press save insert the additional line with the code i sent last time:

Windows("StockCard 2004.xls").Activate
ActiveWorkbook.Save
ActiveWindow.Close

To stop the read only dialog appearing place:

Application.DisplayAlerts = False

At the beginning of your code. That should stop that. But be careful as it will not show you any warnings.

Hope this helps

why not let your vba open the file you need before the formula is run and then close it again when finished.

This will open a work book:

Workbooks.Open "'D:\My Work\Stock\StockCard 2004.xls"

To close it again use:

Windows("StockCard 2004.xls").Activate
ActiveWindow.Close

Hi all,

I am getting a number of return errors from VLOOKUP which I expect and need to move these rows to Sheet2. But there are two columns (d, e) which return these errors and I only need to move the ones where both columns return an error.

Can anybody help me with this?

Thanks

In the ammended spreadsheet i mentioned that 10000R would be followed by OP02.1000R

I have just ammended your sample spreadsheet to include OP02 before the value on Sheet1 and Sheet2 but it does not work now.

I presume the formula needs to be changed???

See ammended spredsheet, sorry

XLS File attached:

Sheet1 contains the values to be looked up and Sheet2 contains the table of data. In Sheet1 there is a value of 20000R, on Sheet2 this is 20000R.104.

The VLOOKUP needs to recognise that 20000R.104 is the same as 20000R.

Is this making sense now?

Thanks for your help guys!

Yes Will they will always appear to the left of the other values

I will attach a sample spreadsheet so you can understand better. Thanks

Hi all,

I am using VLOOKUP to insert data from one spreadsheet to another. The data it looks up from one is not always the same in the other but within the text on the other spreadsheet it does have the value from the first spreadsheet.

For example:

In the first spreadsheet I will need to lookup in the second "10000R"

In the second spreadsheet this may appear as "10000R.140"

These two values relate to the same thing but are slightly different. Is there anyway I can use VLOOKUP to lookup the value within the value in the 2nd spreadsheet and not just the whole value?

I hope this makes sense, if you need anymore background info just ask.

Thanks!

Yes that helps thank you.
For the Lrow =
How can i specify all rows?

The last post i made may not have made sense. There are two columns in my spreadsheet with lookup formulas. Some i know will have a #N/A value. Those rows which bring up a value of #N/A need to be copied to another spreadsheet (e.g. Sheet2)

How can this be done? Is it similar to the delete method above?

Thanks

That worked great, thanks!

One more thing! How do i copy rows which return #N/A in two out of two columns with lookup formulas?

Thank You! That worked perfectly! Within that snippet of code you sent how can I specify more than just "SEOP".

Do I put "SEOP" "SUKOP"

Thanks