Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.
Dynamic Range Update Problem After Adding Records to Sheet in Excel 2007
All,
I have had a brief look around the knowledge section but cannot seem to locate anything that matches my needs here.
I have a simple workbook with two sheets in it named "InvoiceHeader" and "Ledger".
In the sheet named "Ledger", I have added a button to add a set of predefined records to quickly assist whoever can help me with solving this problem.
Now there are two items I need to resolve here.
1. Each time I add a new row of records to either of the sheets, I need the ranges for each of them to grow automatically immediately after the addition has been completed;
After adding a row of data, I should be able to query the newly resized range to check the specific record that was just added. I am using forms to do this and in the Sheet named Ledger, I am most interested in the field NDate in column(a).
2. I want to do this in the most efficinet manner to avoid slowing the workbook down as it will grow quite large over time; i.e. Index, Match, Offset...
For Example:
I have had a few shots doing this on my own but am getting frustrated because I cannot get them to work the way I want them to.
Enclosed is a clean file for reference.
CF
Sample.xlsm
I have had a brief look around the knowledge section but cannot seem to locate anything that matches my needs here.
I have a simple workbook with two sheets in it named "InvoiceHeader" and "Ledger".
In the sheet named "Ledger", I have added a button to add a set of predefined records to quickly assist whoever can help me with solving this problem.
Now there are two items I need to resolve here.
1. Each time I add a new row of records to either of the sheets, I need the ranges for each of them to grow automatically immediately after the addition has been completed;
After adding a row of data, I should be able to query the newly resized range to check the specific record that was just added. I am using forms to do this and in the Sheet named Ledger, I am most interested in the field NDate in column(a).
2. I want to do this in the most efficinet manner to avoid slowing the workbook down as it will grow quite large over time; i.e. Index, Match, Offset...
For Example:
=Ledger!$A$2:INDEX(Ledger!$A:$W,MATCH(REPT("z",20),Ledger!$A:$W))
I have had a few shots doing this on my own but am getting frustrated because I cannot get them to work the way I want them to.
Enclosed is a clean file for reference.
CF
Sample.xlsm
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hello,
try this for the dynamic range:
=Ledger!$A$2:INDEX(Ledger!
Some explanation why your original formula does not work:
=Ledger!$A$2:INDEX(Ledger!
Match() needs a single column or single row reference, instead of the columns A:W. If we assume that column A always has data, then it is sufficient to watch column A only, so we can adjust to
=Ledger!$A$2:INDEX(Ledger!
Column A has numeric data (dates), though, so we should look for numbers instead of text. Let's replace the very long "zzzzzzzzzzzz" string with a very big number, for example 99^99 and just to make sure that we know what we're doing, we don't just omit the third parameter of the Match() function, but set it to 1 (which is the default when omitted). I like to be sure what parameter is being used, so I tend to display defaults in cases like these.
=Ledger!$A$2:INDEX(Ledger!
This should get us the last row in the sheet. The Index function is looking at all columns A to W, though, and that means that we also have to specify which column we want to look at. -- Unless we target just one column. Then we can omit the column reference and provide just the row reference. Assuming that the number of columns is static and W is the last column in the table, we can change the formula to
=Ledger!$A$2:INDEX(Ledger!
That's it.
cheers, teylyn
try this for the dynamic range:
=Ledger!$A$2:INDEX(Ledger!
Some explanation why your original formula does not work:
=Ledger!$A$2:INDEX(Ledger!
Match() needs a single column or single row reference, instead of the columns A:W. If we assume that column A always has data, then it is sufficient to watch column A only, so we can adjust to
=Ledger!$A$2:INDEX(Ledger!
Column A has numeric data (dates), though, so we should look for numbers instead of text. Let's replace the very long "zzzzzzzzzzzz" string with a very big number, for example 99^99 and just to make sure that we know what we're doing, we don't just omit the third parameter of the Match() function, but set it to 1 (which is the default when omitted). I like to be sure what parameter is being used, so I tend to display defaults in cases like these.
=Ledger!$A$2:INDEX(Ledger!
This should get us the last row in the sheet. The Index function is looking at all columns A to W, though, and that means that we also have to specify which column we want to look at. -- Unless we target just one column. Then we can omit the column reference and provide just the row reference. Assuming that the number of columns is static and W is the last column in the table, we can change the formula to
=Ledger!$A$2:INDEX(Ledger!
That's it.
cheers, teylyn
Hi teylyn,
Thanks so much for the effort you put into answering my question.
I am unsure if you managed to look into the range names in my workbook but followig your guidelines, here is what I did:
1. I left the existing named range tblLedger as it is =Ledger!$A$1:$W$12 because this is the range I am extracting SQL queries on. Most importantly, this is the one I need to update properly for me so I can execute the following types of queries against it like a table:
2. I changed the range named LedgerPush from what it was i.e. =Ledger!$A$2:INDEX(Ledger!
I might be missing something but please do pardon me as I cannot seem to spot it.
CF
Thanks so much for the effort you put into answering my question.
I am unsure if you managed to look into the range names in my workbook but followig your guidelines, here is what I did:
1. I left the existing named range tblLedger as it is =Ledger!$A$1:$W$12 because this is the range I am extracting SQL queries on. Most importantly, this is the one I need to update properly for me so I can execute the following types of queries against it like a table:
'Specifying connections ->>
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
'Identify the workbook we are using ->>
wkb = Application.ThisWorkbook.FullName
'STEP 1 - TOTAL INCOME ###########################################
'Open connection to the workbook ->>
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & wkb & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
'Loading the selected invoice status range into a recordset ->>
rst.Open "SELECT Sum([ITotal]) AS [TOTALINCOMING] " & _
"FROM [tblLedger] WHERE [FY] =" & uFY & "", cnn, adOpenStatic
v = rst.RecordCount
If v > 0 Then
rst.MoveFirst
'Collecting the rs values ->>
vTOTALINCOMING = rst("TOTALINCOMING").value
'Now add all the values accordingly and assign them to the screen controls for the user to review ->>
frmInvoiceManagement.txtTotIncome.value = Format((vTOTALINCOMING), "$#,##0.00")
End If
2. I changed the range named LedgerPush from what it was i.e. =Ledger!$A$2:INDEX(Ledger!
I might be missing something but please do pardon me as I cannot seem to spot it.
CF
Just an idea, is there a way I can grow it with my code after I have added the record?
i.e:
i.e:
'Now resize the range that the data was just added to ->>
Set rng = wksLedger.Range("A" & wksLedger.Rows.Count).End(xlUp).Offset(1, 0) 'append to end
rng.Resize(, UBound(vData) + 1).value
I have not looked into the VBA code at all.
If a dynamic range is set up correctly, it does not require any VBA to change it.
Why do you use a static range with just 12 rows for tblLedger? You could use the LedgerPush range instead, which will always encompass the whole table. No VBA interaction required.
cheers, teylyn
If a dynamic range is set up correctly, it does not require any VBA to change it.
Why do you use a static range with just 12 rows for tblLedger? You could use the LedgerPush range instead, which will always encompass the whole table. No VBA interaction required.
cheers, teylyn
Hi,
I have just picked something up:
When I type in LedgerPush into the namebar, it correctly highlights all the data for all the rows that have data from A2 ~ W20. Just as yu intended it to work.
However, I cannot use the LedgerPush range name in my VBA for SQL data extraction / manipulation because excel does not recognize it?
The two are range names are supposed to compliment each other. One is a dummy Push, the other is the actual range update that I can use for data connections etc...
CF
I have just picked something up:
When I type in LedgerPush into the namebar, it correctly highlights all the data for all the rows that have data from A2 ~ W20. Just as yu intended it to work.
However, I cannot use the LedgerPush range name in my VBA for SQL data extraction / manipulation because excel does not recognize it?
The two are range names are supposed to compliment each other. One is a dummy Push, the other is the actual range update that I can use for data connections etc...
CF
Here is a screenshot of the VBA error I am getting with this problem...
Can you now see my dilemma with Excel not recognising the range name LedgerPush?
CF
LedgerPush-Error.jpg
Can you now see my dilemma with Excel not recognising the range name LedgerPush?
CF
LedgerPush-Error.jpg
OK, so the dynamic range via the range name does not work in VBA. You can adjust the range with this code
Set Rng = wksLedger.Range("A2:W" & Cells(Rows.Count, "A").End(xlUp).Row)
Hi,
I changed this as well but now getting another error "Object variable or Block Not Set"
I actually thought this would be quite simple but obviously not! Lol..
Code as follows:
I changed this as well but now getting another error "Object variable or Block Not Set"
I actually thought this would be quite simple but obviously not! Lol..
Code as follows:
'Activate worksheet ->>
ActiveWorkbook.Sheets("Ledger").Activate
Range("$A$2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.value = strtxtNDate
ActiveCell.Offset(0, 1) = strtxtDate
ActiveCell.Offset(0, 2) = strtxtFY
ActiveCell.Offset(0, 3) = strtxtQuarter
ActiveCell.Offset(0, 4) = strtxtWeekNo
ActiveCell.Offset(0, 5) = strtxtWeekdayNb
ActiveCell.Offset(0, 6) = strtxtICash
ActiveCell.Offset(0, 7) = strtxtIEftpos
ActiveCell.Offset(0, 8) = strtxtICash + strtxtIEftpos
ActiveCell.Offset(0, 9) = strtxtODrinks
ActiveCell.Offset(0, 10) = strtxtOCoffee
ActiveCell.Offset(0, 11) = strtxtOBakery
ActiveCell.Offset(0, 12) = strtxtOSmallGoods
ActiveCell.Offset(0, 13) = strtxtOPremadeFood
ActiveCell.Offset(0, 14) = strtxtOFruitVeges
ActiveCell.Offset(0, 15) = strtxtOMiscellaneous
ActiveCell.Offset(0, 16) = strtxtORent
ActiveCell.Offset(0, 17) = strtxtOEnergy
ActiveCell.Offset(0, 18) = strtxtOInsurance
ActiveCell.Offset(0, 19) = strtxtOTelephone
ActiveCell.Offset(0, 20) = strtxtOGas
ActiveCell.Offset(0, 21) = strtxtOSalariesWages
ActiveCell.Offset(0, 22) = strtxtODrinks + strtxtOCoffee + strtxtOBakery + strtxtOSmallGoods + strtxtOPremadeFood + strtxtOFruitVeges _
+ strtxtOMiscellaneous + strtxtORent + strtxtOEnergy + strtxtOInsurance + strtxtOTelephone + strtxtOGas + strtxtOSalariesWages
Dim rng As Range, wksLedger As Worksheet
Set rng = wksLedger.Range("A2:W" & Cells(Rows.Count, "A").End(xlUp).Row)
'Set back to default ->>
Range("$A$1").Select
'Saving the workbook ->>
ActiveWorkbook.Save
End Sub
Hi,
I managed to get this sorted myself through some research back on some help from dmile some years ago.
In the code of the worksheet "ledger" I added the following snippet:
Then, I updated the named range LedgerPush as per your recommendation to
Then, I still kept my original named range tblLedger as it always was but the code I inserted into the worksheet on change event actually triggers it to dynamically update on the fly.
Pretty clever really!!! Thanks agains dmile champ and thanks teylyn for your help as well.
I have awarded you points for the effort and partial solution.
Enjoy your Xmas break.
CF
I managed to get this sorted myself through some research back on some help from dmile some years ago.
In the code of the worksheet "ledger" I added the following snippet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
ThisWorkbook.Names("tblLedger").RefersTo = Range(ThisWorkbook.Names("LedgerPush").RefersToRange.Address(external:=True))
On Error GoTo 0
End Sub
Then, I updated the named range LedgerPush as per your recommendation to
=Ledger!$A$2:INDEX(Ledger!$W:$W,MATCH(99^99,Ledger!$A:$A,1))
Then, I still kept my original named range tblLedger as it always was but the code I inserted into the worksheet on change event actually triggers it to dynamically update on the fly.
Pretty clever really!!! Thanks agains dmile champ and thanks teylyn for your help as well.
I have awarded you points for the effort and partial solution.
Enjoy your Xmas break.
CF
Premium Content
You need an Expert Office subscription to comment.Start Free Trial