Macro to insert formula result into range with zero values in cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a column with possible Values A thru F for example. I need to insert
any of A thru F (whatever is there) into another range where there are cells
equal to zero in that range (column). There can only be one copy of A thru F
in the new range. Please help if you can, I am blocked on this project. I
appreciate any help given. If this is more appropriate in worksheet
functions, please advise. Posted only in programming.

Thanks
 
I am having trouble visualizing what you are doing. Can you explain it a bit
clearer?
 
I hope this helps. I have finally derived a 31 row column of
percussion/instruments/vocals that I use weekly for a large music production.
I have compared them to last weeks list and they have been matched in the
same location/row number that they occupied last week so that the musical
instrument cabling doesn't change if the musical instrument is being used
again this week. So I now have another column of musical instruments,
vocalists and percussion instruments that are being used this week that are
in need of placement. Row numbers 1-18 are percussion, row 19-24 are
instruments, and rows 25-31 are vocals. The extras for the week have been
identified as percussion, instrument, and vocal. Now I need to put them
where they belong with some overlap if needed, ie, row 1-19 could be
percussion, or row 18-26 could be instruments.

I finally derived this formula to get rid of the #N/A that was coming up in
my vlookup when I would get no hit/match
=IF(ISNA(VLOOKUP($H2,$F$2:$G$50,2,0)),0,VLOOKUP($H2,$F$2:$G$50,2,0))
I end up with cells containing zero where I am not using what was used last
week.
 
Is this better

=IF(ISNA(VLOOKUP($H2,$F$2:$G$50,2,0)),"",VLOOKUP($H2,$F$2:$G$50,2,0))
 
Bob,

Thank-you, but problem wasn't with the formula. I do want the zero instead
of the #n/a. The issue is inserting the extra unplaced instruments mentioned
above in the cells egual to zero. Can you help with that?

JPS
 
Do you mean that you want to be able to add more instruments to the second
list but only as long as they aren't already there? A "unique" approach that
doesn't allow duplicate instruments on the second column?
 
Doctor G,

This should not be a problem because I have already matched instruments
previously. Now I olnly need to place the instruments that did not match
this week/ie they are instruments not used last week and not already in the
list. Please help if you can.

JPS
 
I cannot visualize what the spreadsheet looks like, what information is your
unique key for your vlookup and last but not least the sequencing of the
inputs. By this I mean where and when you input the necessary info for last
week and this week and what will be taken out or added next week. Do you keep
week numbers, dates?

If you don't mind, set up a "written" 3 instrument spreadsheet (put "Soso"
in A1, Vlookup(vxvxvxvx...) in G1 and so on) that I can follow to duplicate
what you have and I will be glad to give it a try.
 
would you like me to email you the spreadsheet.? I could probably clean it
up and get it to you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Find a value in a sheet and replace it with another value if it matches 8
Insert rows at change in cell value 3
If Statement to fill in column to last row 6
Need to run a Macro to .... 3
Combining Text based on Values 8
Inserting cells using VBA 3
inserting range value in call 1

Back
Top