R1C1 format and variable ranges

[Guest]

i'm using UsedRange.Columns.Count on a download file to determine number of
columns (which will increase over time) and am using a lookup formula to pull
data out of that file and into a model.

i am copying that formula over the same number of columns in the model.
problem is, the recorded macro has fixed values which won't handle an
increase. what kind of code will allow my formula to be copied over a
changing number of columns? if this has been discussed previously please
reference those posts, thanks!

 

[Guest]

It would be easier to answer if you showed your code for the lookup.

 

[Guest]

hi barb, thanks for response, here's the formula. note, dropper is variable
which is determined by date. just realized, could i replace the 6 in
ActiveCell.Offset with a variable based on RangeUsed.Columns.Count?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(dropper - 1) & "]C,MW118487750.xls!C2:C9,8,FALSE)"
ActiveCell.Copy
Range(Selection, ActiveCell.Offset(, 6)).Select

 

[Guest]

In order to ensure that the data is being extracted from the right column,
you need to change this:

"=VLOOKUP(R[" & -(dropper - 1) & "]C
to something like this
"=VLOOKUP(R[" & -(dropper - 1) & "]C1

Make sense?

HTH,
Barb Reinhardt

hi barb, thanks for response, here's the formula. note, dropper is variable
which is determined by date. just realized, could i replace the 6 in
ActiveCell.Offset with a variable based on RangeUsed.Columns.Count?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(dropper - 1) & "]C,MW118487750.xls!C2:C9,8,FALSE)"
ActiveCell.Copy
Range(Selection, ActiveCell.Offset(, 6)).Select

It would be easier to answer if you showed your code for the lookup.

 

[Guest]

not sure why C isn't referenced with a number, obtain good results across
columns, but will add the column number. any ideas bout how to copy to a
variable amount of columns

 

[Guest]

shouldn't C be zero? that value is relative to cell where formula is, and
it's picking up info from the top row of the same column . .

 

[Dave Peterson]

It's difficult to guess what you want.

We don't know what dropper is equal to. We don't know the location of the
activecell. And we don't know what that formula is really trying to do.

You may need to give more description to get more help.

shouldn't C be zero? that value is relative to cell where formula is, and
it's picking up info from the top row of the same column . .

In order to ensure that the data is being extracted from the right column,
you need to change this:

"=VLOOKUP(R[" & -(dropper - 1) & "]C
to something like this
"=VLOOKUP(R[" & -(dropper - 1) & "]C1

Make sense?

 

[Guest]

You may need to give more description to get more help.

hi dave, in original post i was trying to figure way to copy to a range that
would be changing over time. let me try to explain: we download an excel
file daily from bank which lists account balances. we have a model which
needs to be updated daily with the latest balances. code below, "box" is
named range which is column of dates. hope it's not too confusing . .

currdate = Application.InputBox("Enter number of Duetsche Bank download.
Please enter number only, e.g., if filename: MW123456, then enter: 123456",
"MW file number", 1184775073313#, , , , , 2)
Dim dbname As String
dbname = "MW" & currdate & ".xls"
Dim dbsheet As String
dbsheet = "MW" & currdate

Windows(dbname).Activate
Range("a2").Select
Dim latest As Single
latest = ActiveCell.Value

Windows("model.xls").Activate
Sheets("daily balances").Select
Cells(Rows.Count, "a").End(xlUp).Select
Dim bkldate As Single
bkldate = ActiveCell.Value

If latest <= bkldate Then
msg = "Do you want to overwrite existing values?"
Style = 3 + 32 + 0
query = MsgBox(msg, Style)
If query = vbYes Then
Dim dropper As Integer
dropper = Application.Match(latest, Range("box"))
Cells(dropper, 1).Activate
Else: Stop
End If
End If

If latest + 1 = bkldate Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = latest
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(dropper - 1) & "]C,MW" & currdate &
".xls!C2:C9,8,FALSE)"
ActiveCell.Copy
Range(Selection, ActiveCell.Offset(, 6)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

i'm trying to figure out way to make fourth line up be a variable which
would copy over the same number of columns in download file

 

[Dave Peterson]

I still think you haven't shared enough information.

There are a lot of select's and activate's in your code. It's difficult to see
what's going on (for me, at least).

I started rewriting your code into something I like (a common problem for me!),
but I got to a point where I didn't know enough to go any further.

Anyway, I did this:

Option Explicit
Sub testme()

Dim CurrDate As Variant 'could be boolean
Dim DBName As String
Dim DBSheet As String
Dim Query As Long
Dim Dropper As Variant
Dim BklDate As Double
Dim Latest As Double
Dim DestCell As Range
'this is one more than you need
'date is in column A???
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

DBName = "MW" & CurrDate & ".xls"
DBSheet = "MW" & CurrDate

With Workbooks(DBName).Worksheets(DBSheet)
Latest = .Range("a2").Value
'if you hit ctrl-end in that dl file,
'do you go to the last column?
NumberOfColsInDLFile = .Cells.SpecialCells(xlCellTypeLastCell).Column
'if it over shoots,
'can you trust row 1 to get the number of columns used
NumberOfColsInDLFile = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set DLLookupRng _
= .Range("b1", .Cells(1, NumberOfColsInDLFile)).EntireColumn
End With

With Workbooks("Model.xls").Worksheets("daily balances")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Value
BklDate = DestCell.Value

If Latest <= BklDate Then
Query = MsgBox(prompt:="Do you want to overwrite existing values?",
_
Buttons:=vbYesNo + vbQuestion)

If Query = vbYes Then
'If you wanted an exact match, you'd need that 3rd parm = 0
Dropper = Application.Match(Latest, .Range("box"))

If IsError(Dropper) Then
MsgBox "No match for: " & Latest & " in the Box column"
Exit Sub
End If

Set DestCell = .Cells(Dropper, 1)
Else
Exit Sub
End If
End If

If Latest + 1 = BklDate Then
Set DestCell = DestCell.Offset(1, 0)
DestCell.Value = Latest
End If

'go to the right one column
Set DestCell = DestCell.Offset(0, 1)

'and this is where I got too confused.
destcell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(Dropper - 1) & "]C,MW" & _
CurrDate & ".xls!C2:C9,8,FALSE)"
ActiveCell.Copy
Range(Selection, ActiveCell.Offset(, 6)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

End Sub

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.

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.

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]).

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

But I don't think you'll end up with something that works.

You may need to give more description to get more help.

hi dave, in original post i was trying to figure way to copy to a range that
would be changing over time. let me try to explain: we download an excel
file daily from bank which lists account balances. we have a model which
needs to be updated daily with the latest balances. code below, "box" is
named range which is column of dates. hope it's not too confusing . .

currdate = Application.InputBox("Enter number of Duetsche Bank download.
Please enter number only, e.g., if filename: MW123456, then enter: 123456",
"MW file number", 1184775073313#, , , , , 2)
Dim dbname As String
dbname = "MW" & currdate & ".xls"
Dim dbsheet As String
dbsheet = "MW" & currdate

Windows(dbname).Activate
Range("a2").Select
Dim latest As Single
latest = ActiveCell.Value

Windows("model.xls").Activate
Sheets("daily balances").Select
Cells(Rows.Count, "a").End(xlUp).Select
Dim bkldate As Single
bkldate = ActiveCell.Value

If latest <= bkldate Then
msg = "Do you want to overwrite existing values?"
Style = 3 + 32 + 0
query = MsgBox(msg, Style)
If query = vbYes Then
Dim dropper As Integer
dropper = Application.Match(latest, Range("box"))
Cells(dropper, 1).Activate
Else: Stop
End If
End If

If latest + 1 = bkldate Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = latest
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(dropper - 1) & "]C,MW" & currdate &
".xls!C2:C9,8,FALSE)"
ActiveCell.Copy
Range(Selection, ActiveCell.Offset(, 6)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

i'm trying to figure out way to make fourth line up be a variable which
would copy over the same number of columns in download file

 

[Guest]

hi dave, thanks for reply and appreciate your taking the time to rewrite my
code. i use a combination of the macro recorder (that uses lots of selects,
activates) and picking stuff off this website, so yeah it's a hodgepodge.

can't work on this today but please check back next week and i'll try to
answer all your questions, have a great weekend!

I still think you haven't shared enough information.

There are a lot of select's and activate's in your code. It's difficult to see
what's going on (for me, at least).

I started rewriting your code into something I like (a common problem for me!),
but I got to a point where I didn't know enough to go any further.

Anyway, I did this:

Option Explicit
Sub testme()

Dim CurrDate As Variant 'could be boolean
Dim DBName As String
Dim DBSheet As String
Dim Query As Long
Dim Dropper As Variant
Dim BklDate As Double
Dim Latest As Double
Dim DestCell As Range
'this is one more than you need
'date is in column A???
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

DBName = "MW" & CurrDate & ".xls"
DBSheet = "MW" & CurrDate

With Workbooks(DBName).Worksheets(DBSheet)
Latest = .Range("a2").Value
'if you hit ctrl-end in that dl file,
'do you go to the last column?
NumberOfColsInDLFile = .Cells.SpecialCells(xlCellTypeLastCell).Column
'if it over shoots,
'can you trust row 1 to get the number of columns used
NumberOfColsInDLFile = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set DLLookupRng _
= .Range("b1", .Cells(1, NumberOfColsInDLFile)).EntireColumn
End With

With Workbooks("Model.xls").Worksheets("daily balances")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Value
BklDate = DestCell.Value

If Latest <= BklDate Then
Query = MsgBox(prompt:="Do you want to overwrite existing values?",
_
Buttons:=vbYesNo + vbQuestion)

If Query = vbYes Then
'If you wanted an exact match, you'd need that 3rd parm = 0
Dropper = Application.Match(Latest, .Range("box"))

If IsError(Dropper) Then
MsgBox "No match for: " & Latest & " in the Box column"
Exit Sub
End If

Set DestCell = .Cells(Dropper, 1)
Else
Exit Sub
End If
End If

If Latest + 1 = BklDate Then
Set DestCell = DestCell.Offset(1, 0)
DestCell.Value = Latest
End If

'go to the right one column
Set DestCell = DestCell.Offset(0, 1)

'and this is where I got too confused.
destcell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(Dropper - 1) & "]C,MW" & _
CurrDate & ".xls!C2:C9,8,FALSE)"
ActiveCell.Copy
Range(Selection, ActiveCell.Offset(, 6)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

End Sub

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.

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.

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]).

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

But I don't think you'll end up with something that works.

You may need to give more description to get more help.

hi dave, in original post i was trying to figure way to copy to a range that
would be changing over time. let me try to explain: we download an excel
file daily from bank which lists account balances. we have a model which
needs to be updated daily with the latest balances. code below, "box" is
named range which is column of dates. hope it's not too confusing . .

currdate = Application.InputBox("Enter number of Duetsche Bank download.
Please enter number only, e.g., if filename: MW123456, then enter: 123456",
"MW file number", 1184775073313#, , , , , 2)
Dim dbname As String
dbname = "MW" & currdate & ".xls"
Dim dbsheet As String
dbsheet = "MW" & currdate

Windows(dbname).Activate
Range("a2").Select
Dim latest As Single
latest = ActiveCell.Value

Windows("model.xls").Activate
Sheets("daily balances").Select
Cells(Rows.Count, "a").End(xlUp).Select
Dim bkldate As Single
bkldate = ActiveCell.Value

If latest <= bkldate Then
msg = "Do you want to overwrite existing values?"
Style = 3 + 32 + 0
query = MsgBox(msg, Style)
If query = vbYes Then
Dim dropper As Integer
dropper = Application.Match(latest, Range("box"))
Cells(dropper, 1).Activate
Else: Stop
End If
End If

If latest + 1 = bkldate Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = latest
End If

ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(dropper - 1) & "]C,MW" & currdate &
".xls!C2:C9,8,FALSE)"
ActiveCell.Copy
Range(Selection, ActiveCell.Offset(, 6)).Select
ActiveSheet.Paste
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

i'm trying to figure out way to make fourth line up be a variable which
would copy over the same number of columns in download file

 

[Guest]

hi dave, some things have changed since last week. instead of pulling data
from the download file, that data will be copied into a tab in workbook.
also, need to clarify something, the deutsche bank data file lists accounts
row by row (with critical data in eighth column) while our worksheet shows
accounts spread over columns.

didn't mention this in original post because thought it would be too
confusing to explain when all i wanted was macro that would be able to copy
over a varying set of columns. hope that makes sense. problem is, when i
sent my code, forgot to make this clear so i apologize for the confusion.

here's code right now, the last couple rows where "history_row" is defined,
i can't get to work (my old code, using ActiveCells and Selections, copies
over correct range but can't seem to convert it to something that looks like
your code)

option explicit
Sub test()

Dim latest As Long
Dim bkldate As Long
Dim rows_used As Integer
Dim cols_used As Integer
Dim dropper As Integer
Dim warning As Long
Dim warning2 As Long
Dim query As Long
Dim destcell As Range
Dim history_row As Range
Dim msg As String
Dim style As String


With Worksheets("db output")
latest = .Range("a2").Value
rows_used = .UsedRange.Rows.Count
End With

With Worksheets("px hist")
cols_used = .UsedRange.Columns.Count
If rows_used <> cols_used Then
msg = "WARNING!! Number of accounts from Deutsche Bank does not equal the
number of accounts in this spreadsheet. Please resolve."
style = 0 + 16
warning = MsgBox(msg, style)
Exit Sub
End If

Set destcell = .Cells(.Rows.Count, "a").End(xlUp)
bkldate = destcell.Value

If latest <= bkldate Then
msg = "Do you want to overwrite existing values?"
style = 3 + 32 + 0
query = MsgBox(msg, style)
If query = vbYes Then
dropper = Application.Match(latest, .Range("a:a"), 0)
If IsError(dropper) Then
msg = "You are entering data for a date not currently included in this
schedule. You must Insert a line, with date, to the Px_Hist tab, in order to
continue."
style = 0 + 32
warning2 = MsgBox(msg, style)
Exit Sub
End If
destcell = .Cells(dropper, 1)
Else: Exit Sub
End If
End If

If latest > bkldate Then
Set destcell = destcell.Offset(1, 0)
destcell.Value = latest
dropper = Application.Match(latest, .Range("a:a"))
End If


Set destcell = destcell.Offset(0, 1)
destcell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(dropper - 1) & "]C,'DB output'!C2:C9,8,FALSE)"
destcell.Copy
Set history_row = .Range(destcell, destcell.Offset(, rows_used - 2))
history_row.Paste
history_row.Copy
history_row.PasteSpecial Paste:=xlPasteValues
End With

End Sub

 

[Dave Peterson]

But you didn't explain what you wanted.

Or answer any of the questions I asked.

hi dave, some things have changed since last week. instead of pulling data
from the download file, that data will be copied into a tab in workbook.
also, need to clarify something, the deutsche bank data file lists accounts
row by row (with critical data in eighth column) while our worksheet shows
accounts spread over columns.

didn't mention this in original post because thought it would be too
confusing to explain when all i wanted was macro that would be able to copy
over a varying set of columns. hope that makes sense. problem is, when i
sent my code, forgot to make this clear so i apologize for the confusion.

here's code right now, the last couple rows where "history_row" is defined,
i can't get to work (my old code, using ActiveCells and Selections, copies
over correct range but can't seem to convert it to something that looks like
your code)

option explicit
Sub test()

Dim latest As Long
Dim bkldate As Long
Dim rows_used As Integer
Dim cols_used As Integer
Dim dropper As Integer
Dim warning As Long
Dim warning2 As Long
Dim query As Long
Dim destcell As Range
Dim history_row As Range
Dim msg As String
Dim style As String

With Worksheets("db output")
latest = .Range("a2").Value
rows_used = .UsedRange.Rows.Count
End With

With Worksheets("px hist")
cols_used = .UsedRange.Columns.Count
If rows_used <> cols_used Then
msg = "WARNING!! Number of accounts from Deutsche Bank does not equal the
number of accounts in this spreadsheet. Please resolve."
style = 0 + 16
warning = MsgBox(msg, style)
Exit Sub
End If

Set destcell = .Cells(.Rows.Count, "a").End(xlUp)
bkldate = destcell.Value

If latest <= bkldate Then
msg = "Do you want to overwrite existing values?"
style = 3 + 32 + 0
query = MsgBox(msg, style)
If query = vbYes Then
dropper = Application.Match(latest, .Range("a:a"), 0)
If IsError(dropper) Then
msg = "You are entering data for a date not currently included in this
schedule. You must Insert a line, with date, to the Px_Hist tab, in order to
continue."
style = 0 + 32
warning2 = MsgBox(msg, style)
Exit Sub
End If
destcell = .Cells(dropper, 1)
Else: Exit Sub
End If
End If

If latest > bkldate Then
Set destcell = destcell.Offset(1, 0)
destcell.Value = latest
dropper = Application.Match(latest, .Range("a:a"))
End If

Set destcell = destcell.Offset(0, 1)
destcell.FormulaR1C1 = _
"=VLOOKUP(R[" & -(dropper - 1) & "]C,'DB output'!C2:C9,8,FALSE)"
destcell.Copy
Set history_row = .Range(destcell, destcell.Offset(, rows_used - 2))
history_row.Paste
history_row.Copy
history_row.PasteSpecial Paste:=xlPasteValues
End With

End Sub

 

[Guest]

hi dave, answers first, see below.

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]

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

hi dave, answers first, see below.

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

 

[Guest]

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.

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

hi dave, answers first, see below.

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]

You must live a good life!

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

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.

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

hi dave, answers first, see below.

:

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