Lookup?

[green fox]

I have an spreadsheet with two cells on it. Created from a vax file,
with Monarch. Cell A1 is a date, cell B1 is a value. I want to plug
that value into a second spreadsheet. Column A of the second sheet
contains dates, and column E contains numbers. I want to plug the value
into column E, Row(date).The two cell worksheet changes every day.

I've been toying with index, vlookup, match and trying anything I can
think of. I'm learning stuff but I don't seem to be getting any
closer to my goal. Can someone provide a few hints to point me in the
right direction please.

Dazed and confused.
Green Fox

[hideki]

Does you have duplicates dates?

[green fox]

in workbook 2, I have 31 dates-- in this case one for each day of
august. Workbook 1 is an august date.

Workbook 2 has a different worksheet for each month of the fiscal year.

[hideki]

I'm not sure if this is the best solution but I think you can just loops to the date column and check if the date same to the column A in 2nd sheet. If the same date found, copy column B value to 2nd sheets column E.

If your two sheets are in the same workbook, you can try to run this procedure in the 1st sheet

Hope this help,
hideki

Sub CopyTo2ndSheet()

Dim rngCell As Range 'cells
Dim rngWork As Range 'working range
Dim lngLastRow As Long 'last row
Dim ws2ndSheet As Worksheet '2nd worksheet
Dim lngRow As Long 'row in 2nd sheet

'"Sheet3" is the name of your 2nd sheet
Set ws2ndSheet = Sheets("Sheet3")
'Last row in 2nd sheet assuming column A always contains data
lngLastRow = ws2ndSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Your 1st sheet date range
Set rngWork = Range("A2:A32")

'This will loop from A2 to A32 (working range)
For Each rngCell In rngWork
'loop in the 2nd sheet until last row
For lngRow = 2 To lngLastRow
'If current cell value = column A in 2nd sheet value
'copy the cell next column value (Column B) to the next column
'in the 2nd sheet
If rngCell.Value = ws2ndSheet.Cells(lngRow, "A").Value Then
ws2ndSheet.Cells(lngRow, "E") = rngCell.Offset(0, 1).Value
End If
Next
Next

End Sub

[green fox]

Hi Hideki,

Thanks for your suggestion, before I try it though, could this be
adapted to work with the two sheets in different workbooks, or will I
have to arrange it so both sheets are in the same workbook.

thanks again,

Andy

[green fox]

Hi again Hideki,

your solution works well, as long as the two sheets are in the same
workbook. So I'll need to alter some other code to put the workbook1
sheet into workbook2. It still seems like I should be able to do it the
other way... I'll keep playing with that too.

Thank you for your time and patience

Andy

[hideki]

Below code is far from satisfactory. It may work for two different workbook. Please alter to suit your situation. Warning the isn't any error handling in this code, you may need that if running in a complex application.

I hope you understand my dirty/unorganized code:

Sub CopyTo2ndSheet()

Dim rngCell As Range 'cells
Dim rngWork As Range 'working range
Dim lngLastRow As Long 'last row
Dim ws2ndSheet As Worksheet '2nd worksheet
Dim lngRow As Long 'row in 2nd sheet
Dim wbSecond As Workbook '2nd workbook
Dim wbFirst As Workbook 'first workbook
Dim strPath As String '2nd workbook path
Dim strFileName As String '2nd workbook file name

'First workbook is where you run this procedure
Set wbFirst = ActiveWorkbook

'Your 2nd file path -> to make it simple I use same path with
'current book
strPath = ThisWorkbook.Path
'YOur 2nd file name
strFileName = "Book2.xls"

'Open 2nd workbook. Carefull you need back slash between fileName and path
Set wbSecond = Workbooks.Open(strPath & "\" & strFileName)


'"Sheet3" is the name of your 2nd sheet
Set ws2ndSheet = wbSecond.Sheets("Sheet3")
'Last row in 2nd sheet assuming column A always contains data
lngLastRow = ws2ndSheet.Cells(Rows.Count, "A").End(xlUp).Row

'Your 1st sheet date range
wbFirst.Activate
Set rngWork = Range("A2:A32")

'This will loop from A2 to A32 (working range)
For Each rngCell In rngWork
'loop in the 2nd sheet until last row
For lngRow = 2 To lngLastRow
'If current cell value = column A in 2nd sheet value
'copy the cell next column value (Column B) to the next column
'in the 2nd sheet
If rngCell.Value = ws2ndSheet.Cells(lngRow, "A").Value Then
ws2ndSheet.Cells(lngRow, "B") = rngCell.Offset(0, 1).Value
End If
Next
Next

'When finish, close the workbook
'It will ask wether you want to save the change
'You can bypass this warning -> not save or save without asking etc
'If you want to close by yourself, delete this part
wbSecond.Close

End Sub