Loop not copying value into desired cell

Hello All,

I am trying to get data from a table into various cells in another worksheet. I am doing this by selecting a facility from a drop down list in V16 of the sheet I want the data to go to.
I then click a button with the following code in it:

Please Login or Register  to view this content.

It doesn't throw up any errors but when I look at the code execution I can see that it runs through each row in the table but doesn't match the facility, it simply stops there?

The destination for the lookup info is B8 in the EQUFACsheet

Any ideas?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.


Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


Since you are relatively new I'll add them for you on this occasion but please note for the future.

Difficult to say without seeing the workbook. Are you able to upload it?

I note you appear to be using merged cells which is usually a no no since they just cause too many problems. There's a chance it might be something to do with that.

Incidentally you don't need all the .Select stuff. Just address the objects directly in the code.

I fear you are over-complicating this. Where you have perfecly acceptable standard Excel functions then use them. Don't resort to VBA.

In this case all you need in cells like F15 is a formula. Here

Formula:

Please Login or Register  to view this content.

There is no need for any macro to loop through cells to find an copy stuff.
Your code won't work incidentally for the following reason.
Using Cells without an object qualifier is equivalent to ActiveSheet.Cells. So although you've Selected the Data sheet (and as I said previously .Select code is rarely necessary and just slows things down) the active sheet that the Cells() synatx is using is still the Equ Fac sheet containing the button you clicked.

You would have to have used

Please Login or Register  to view this content.

to fully qualify the Cells object. And note the Sheet16. Another lesson - always use VBA sheet code names, the sheet tab name are too easily changed by a user and if changed will cause a hard coded tab name to fail.

And before yuo get too far in your excel design skills please think carefully about using merged cells. Most of us avoid them like the plague. They just cause too many problems. Often a simple format of 'Center across cells is sufficient.

I wrote this before I saw your simplified upload - and note for the future that we generally prefer copies of the production workbook rather than 'simple' versions whic are not representative of the actual layout and detail of the production workbook.

OK, understood.

I think using a function in cells to read from the database is the optimum method.
To achieve your next requirement of passing the changed data back to the database I'd adopt a similar approach to what you were doing.

Create a row above your Input data. I always think it's best to keep this in a fixed range and above data rather than below.
Make that range of cells an exact copy of your data labels, link each of the cells in that row of cells to the specific cells in the Input sheet and name the range, say "NewData"

Now all you need is a macro which identifies the record ID row in the database, then copies and pastes the edited data to that row in te database.
In order to find the data row that matches the edited record I tend to use an =MATCH() function that matches the edited Equipment ID to the data, rather than bothering with some VBA FIND code. So enter the following function in a spare cell and name that cell say "EquipNo"

Formula:

Please Login or Register  to view this content.

Then all the code yuo need is

Please Login or Register  to view this content.