Help with VBA to change range limits

[EssKayKay]

Hello,

I have a dropdown list that is based on dates in a range named LookupDate. The range entry data is based on dates in column “C”, is one column wide, and changes length – but always starts at P33 such as Range(“P33:P200”).

I have a routine that adjusts this range accordingly which is working fine except for one minor issue. If an entry is made in a third column “I”, the corresponding cell in column “P” displays “----“.

Simplified example of formula in column P: IF(I33=0,C33," ----- ")

What’ s happening here is as numerous entries are made in column “I”, the dropdown list displays many “----“ options. Entries in column “I” will start in cell P33 and be consecutive with no rows missed. Therefore as time progresses, the beginning of the dropdown list starts with numerous “----“ entries. I was hoping to find a way to change the range to only include cells with dates.

Example: Cells “P33:P40” could have ---- entries with P41 displaying the first date. All cells between “P41:P200” would have dates. In this scenario, I would want the LookupDate range to be “P41:P200”. The beginning cell in the range would change as more entries are made.

Any suggestions would be appreciated.

Thanks for viewing,
Steve K.

 

[Akuini]

Here's an idea:
1. Use Worksheet_Change with col I as target range, say I33:I1000
2. Any change in col I will trigger a macro to redefine LookupDate range.
The macro could be something like this:

'find the last "-----" in col P
Set c = Range("P33:P" & Rows.Count).Find(What:="-----", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
a = 33
If Not c Is Nothing Then
    a = c.Row + 1
End If

'find the last non empty cell in col P
Set c = Range("P33:P" & Rows.Count).Find(What:="*", LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
    b = c.Row
End If

'the range without "-----", use it to redefine LookupDate range
Debug.Print Range(Cells(a, "P"), Cells(b, "P")).Address

 

[EssKayKay]

Thank you Akuini for your quick response.

1. I understand Worksheet_Change but am confused about #1 – setting col I as target range.
2. I created a macro and inserted your code accordingly. However, when I run the macro I receive a Compile Error – Variable not defined for Set c =.

Unless this is easy to resolve, I believe this may be beyond my ability as the code appears a bit too confusing for me. I may have another way to address this. I am going to try and copy col P, then delete all cells that do not contain a date. I’m not sure how this is going to work but I will keep you posted. I will post a different query on this.

Again, much appreciated.

 

[Akuini]

Could you please upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here? Also, ensure that the link is accessible to anyone.

 

[EssKayKay]

I will try to do upload a sample tomorrow. My worksheet is considerably more complicated.
Thanks again. . .

 

[EssKayKay]

I first tried Dropbox which I've used in the past but they now want me to pay - sorry not going to happen.
I then tried Google Drive but am not sure how to get the link to pass it on. Hope this is it.

Tryme1.xlsm

Amortize Loan Information,Extra Principle Payments ,Start at Payment No:,1 Loan Amount, $ 100,000.00 ,Extra Monthly Payment, $ 300.00 ,Payment Interval:,1 Annual Interest Rate,5.000%,Extra Annual Payment,Payment # (1-12):,1 Term Length (in Months),120, One-Time Payment Date #1 Term Length (...

docs.google.com

-------------------------

I have uploaded a test file of my project (Tryme1.xlsm) to Google Drive.

First off, please forgive me as I must note that I am not a programmer (not even a hacker) so if/when you look at my code you are going to be shocked at how sloppy and inconsistent it all is.

Normally all columns to the right of column M are hidden. I have unhidden all columns so you can see column P which includes the LookupDate range. Also, the worksheet is typically looked; however, I have unlocked it for this review.

If you click the dropdown arrow on K8, K10, or K12 you will see the ----- options. If you look further down the list you will see the next available date as per the LookupDate range (P33:P120). This is where I would like to eliminate all the ----- options and only show available dates.

As example and test –
1. To update payment, you will have to click on the Paym’t Made button on top. If you then enter a date in col M, the corresponding entry in col P will display “-----“ as that date will no longer be available as a dropdown entry.

2. You must then click the Home button on top to post the new entry. If you go back to the dropdown list you should see that the corresponding payment date will no longer be available.

Again, I must stress I realize this is very sloppy coding but for a dumby like me, it works.

Thanks again,
Steve

 

[MARK858]

@EssKayKay , Dropbox is still free for the Basic package

 

[EssKayKay]

Thanks Mark. In the past I thought I did sign up for the "free" version but since I seldom/never use it I had problem. Oh well, I hope it worked on Google Drive. If not, I will try Dropbox again.

 

[MARK858]

Thanks Mark. In the past I thought I did sign up for the "free" version but since I seldom/never use it I had problem. Oh well, I hope it worked on Google Drive. If not, I will try Dropbox again.

Yes, your link works for me but I'll leave it for Akuini to look at as it is he has been working on it

 

[EssKayKay]

Yes, your link works for me but I'll leave it for Akuini to look at as it is he has been working on it

10-4 Understood