PDA

View Full Version : [SOLVED] How to paste X in column A according to the length of column B



truzilla
06-09-2008, 09:38 AM
Hi everyone, this is my second post and I really appreciate everyones help!

So my problem is that I want to paste a Date in column A according to the amount in column B, and do this multiple times. The date in column A is all the same and is supposed to correspond with the line items in column B- each item in B has a Date(value) in column A. But the number of line items in column B can change and basically already has data above it so its a continuous list. See below for example:


A B
x x
x x
x x
_ o
_ o
_ o
_ o

Thus I need a date in column A for each O in column B, but the X's show that both columns have data above them already as the list is a continuing list.

I hope this is clear enough, thank you so much for your help! :hi:

RECrerar
06-09-2008, 09:42 AM
So am I right in thinking that your asking:

If a cell in column B is not empty then you want to automatically put the date in to the corresponding cell in column A?

Do you want the data to be today's date or a specific date?

Sorry, it's not really that clear what you're asking

truzilla
06-09-2008, 09:45 AM
thanks for your reply! I don't mean todays date but a separate date from another sheet. And yes, if there is something in B then I want that date pasted in A, as long as theres something in B. Thanks!

RECrerar
06-09-2008, 09:47 AM
okay so where are the other dates stored? You said they were all the same so do you just have a cell with a date in it in another sheet that you want to read in or does the date that is entered in column a depend on the information in column b?

truzilla
06-09-2008, 09:50 AM
the other date is stored in another sheet in the same book. its just a fixed cell so it should be easily referenced and doesnt change according to the data in B.

RECrerar
06-09-2008, 09:51 AM
I have to go catch a train now, will be back in about an hour.

Let me know:

Exactly where you are getting the data to put into column A is (sheet and cell reference)

If the value of the date will be different for different rows of column B and if so how the values in A and B are related.

Will get back to you soon

truzilla
06-09-2008, 09:56 AM
Thanks for all your help.
The name of the other sheet is TRU and the cell reference is F-15. As an analogy, The data in column B is basically list of groceries bought in a week. They dont change but each list is pasted directly under the previous in column B. Column A is the date the groceries were bought - corresponding to the total list of groceries in Column B. I hope this helps, thank you for your time!

mdmackillop
06-09-2008, 10:08 AM
Sub FillDates()
Dim Rng As Range
Set Rng = Cells(Rows.Count, 2).End(xlUp).Offset(, -1)
Set Rng = Range(Rng, Rng.End(xlUp).Offset(1))
Rng.Value = Sheets("Tru").Range("F15")
End Sub

truzilla
06-09-2008, 11:03 AM
perfect, thank you!

truzilla
06-09-2008, 11:15 AM
Thanks for all your help so far guys - its amazing to see how helpful and gracious people can be. This is a follow up question:

After adding the date to the empty cells...Column C also needs values inputted. Continuing on the last example - column C needs the prices of the groceries inputted. The groceries are listed in another spread sheet which I have already copied...I just need the VBA to select the corresponding grocery item in Column B, so I can paste the price in column C.

Basically, how do you make excel search only a specific portion of the spreadsheet, in this case the area that has column C empty? Thank you so much again for your help!

RECrerar
06-09-2008, 11:18 AM
Alternatively without using a macro, assuming that your list of groceries starts in cell B2, you could type in cell A2


=If(A2<>"",TMU!$F$15,""

And then fill down for how ever many cells you are likely to need (100, 1000, the whole column)

This will put the value in cell F15 into the corresponding cell in column 1 if there is an entry in the cell in column B.

However. If you change the value in cell F15 it will change all the values in column A to that value.

truzilla
06-09-2008, 11:27 AM
thanks RECrerar, any luck with the follow up?

mdmackillop
06-09-2008, 11:27 AM
Sub FillDates()
Dim Rng As Range
Set Rng = Cells(Rows.Count, 2).End(xlUp).Offset(, -1)
Set Rng = Range(Rng, Rng.End(xlUp).Offset(1))
Rng.Value = Sheets("Tru").Range("F15")
'Adjust ranges to suit
For Each cel In Rng
cel.Offset(, 2) = Sheets(3).Columns(1).Find(cel.Offset(, 1)).Offset(, 1)
Next
End Sub

RECrerar
06-09-2008, 12:44 PM
Sorry hadn't read your latest post when I put mine in (but the way as you may have noticed I forgot the final bracket)

Using mdmackillop macro will work, and is probably neater if the sheet is going to get more complex, however if you want to do it without a macro put the following in the cells in column C:


=VLOOKUP(B2,Sheet2!$A$4:$B$30,2)

This assumes that your grogecies list again starts in cell B2, and that the data with the prices is located in sheet 2, with the names on the groceries in Column A and the prices in Column B, and that the data list extends to row 30 (change the range values to suit)

mdmackillop
06-09-2008, 12:54 PM
truzilla,
Both methods are valid. It depends if you want formula links between the two sheets. Of course you can also adjust my code to write in RECrerar's formulae!