You must live a good life!

If you had any skeletons in your closet, it wouldn't have come close to working!

mwam423 wrote:

hi dave, my apologies, i've thoroughly confused you and it's my fault, i've
done poor job of communicating what i wanted the macro to do. what i hope is
some solace for you is that code does exactly what we need, it works!, and
i've learned a great deal about VBA. your time and patience is much
appreciated.

"Dave Peterson" wrote:

I would always specify the worksheet name in a vlookup() formula. In my simple
tests, I always needed them.

This looks like you're plopping a single row of data into row 2 of that "db
output" worksheet. And you keep the old data--you insert a new row 2 and paste
your data. All the existing data shifts down a row--otherwise, it would make
more sense to put in values--not just use formulas.

But I'm still lost at what you want to do.

I'm assuming that the code is in the same workbook as "db output" and "daily
balances".

I don't understand why you need to know the number of columns in the "Db output"
worksheet. You seem to want to make sure you bring back column 8 of that range
(starting in column B--so that would be column I in the worksheet.

This is what it looks like you're doing to me:

You put a date in column A2 of "db output"
You look in column A of "daily balances" to find a match.

If you find a match, then you "merge" (whatever that means) the data into column
B of that "daily balances" worksheet where the date matche.

If you don't find a match, then you "merge" the data into column B of that new
row.

In either case, you use the value in B1 of the "daily balances" worksheet to
find the match--and you retrieve the value from column I and put it in column B.

I don't understand why the data doesn't come from the same row (2) that contains
the date.

This is my next guess:


Option Explicit
Sub testme()

Dim CurrDate As Variant 'could be boolean
Dim Resp As Long
Dim latest As Double
Dim DestCell As Range
Dim res As Variant
Dim NumberOfColsInDLFile As Long
Dim DLLookupRng As Range

CurrDate = Application.InputBox _
(prompt:="Enter number of Duetsche Bank download." _
& " Please enter number only, e.g., if filename: MW123456, " _
& "then enter: 123456", _
Title:="MW file number", Default:=1184775073313#, Type:=2)

If CurrDate = False Then
Exit Sub
End If

With ThisWorkbook.Worksheets("DB Output")
latest = .Range("a2").Value
'can I use row 2 to find the last column used?
NumberOfColsInDLFile = .Cells(2, .Columns.Count).End(xlToLeft).Column
Set DLLookupRng _
= .Range("b1", .Cells(1, NumberOfColsInDLFile)).EntireColumn
End With

With ThisWorkbook.Worksheets("daily balances")
'chcek for a match in column A (that contains the date)
res = Application.Match(CLng(latest), .Range("a:a"), 0)

If IsError(res) Then
'no match for date, so use a new row at the bottom
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1)
'add the date to column A of that new row.
With DestCell
.Value = latest
'change to the format you like
.NumberFormat = "mm/dd/yyyy"
End With
Else
Resp _
= MsgBox(prompt:="Do you want to overwrite existing values?", _
Buttons:=vbYesNo + vbQuestion)

If Resp = vbYes Then
Set DestCell = .Range("a:a")(res) 'where the match is
Else
Exit Sub
End If
End If

With DestCell.Offset(0, 1)
.Formula _
= "=VLOOKUP(B1," & DLLookupRng.Address(external:=True) _
& ",8,false)"
.Value = .Value
End With
End With

End Sub






mwam423 wrote:

hi dave, answers first, see below.

"Dave Peterson" wrote:

I expected to see a workbook name and worksheet name in the lookup table--and
that would mean it would look like:

=vlookup(zzzz,'[book3.xls]Sheet1'!$B:$i,9,FALSE)
But I didn't see that.

since we're copying the data into a tab, there isn't another workbook. the
tab where the data range is now is: 'db output'. in the original code the
workbook name is referenced: MW"&CurrDate&".xls. it was a single tab file,
with the tab name: MW + CurrDate. if a file only has one sheet does excel
need to specify that sheet.

I wanted to use that range variable: DLLookupRng in the formula and let excel
figure out the syntax.

I'm not sure why the first column with the formula gets the value in the 8th
column in the table.

i was hoping my explanation of the different way data is laid out between
download file and history in the model made this clear

Maybe you can share an example of where the formula will go--a real address.
And where the data is being picked up from (r[-dropper+1]).

the formula goes one of two places, the second column of either the row
below the current bottom row of the history tab, as later dates are added to
table, or the row of data that has same date as the current download.
(download is done daily and normally it will just keep updating the history
tab with latest data, but occasionally there will be a change, or correction
to previously entered data, and we would create a download file for date with
correction and then update the history tab.

dropper variable takes the date from the download file, figures out which
row it needs to place data. the term: ( R[dropper - 1], C ) is relative
value, it always picks up information in top row of the column. across the
top of history tab is code deutsche banks uses to identify accounts. this is
the second column in the download file.

And what happens in those other columns.
But there is a variable named NumberOfColsInDLFile that you can use in the
..offset.
Range(Selection, ActiveCell.Offset(, NumberOfColsInDLFile -1)).Select

Not sure above are questions. within code were these comments:

if you hit ctrl-end in dl file do you go to last column? yes, although i'm
not dealing with dl file anymore

if it overshoots can you trust row1 to get the number of columns used? yes

if you wanted an exact match, you'd need 3rd parameter. yes, we want that
and have included in latest code

hope that answers some of your queries, as to what i would like . . how to
copy to a range named "history_row"?
am i defining history_row correctly? as i said before the old code works,
i'm just trying to keep the code consistent

--

Dave Peterson


--

Dave Peterson