Message Box: Request Info and take action based on it

[RLCornish]

I have a workbook that I've created that I continually add data to. I was wondering if there's a way once I paste the data in to have a message box, or something of the kind, pop-up asking for 2 pieces of info, "Date_Thru" & the "Type". THEN using the 2 pieces of info I provide automatically populate that data in cells relative to the rows I just added. Here's my thought process...

• Copy a range of cells from 'Converter' wkbk (hate the copy/paste but can't think of how to eliminate this just yet)
• Paste to 'Control' wkbk first blank line, cells D:Y...however many rows (the # of rows varies)
• Message Box pops up asking for "Date_Thru" and "Type"
• I type these details in the box, or if that's not possible somewhere else
• Those details then populate in A:B for the same rows I just added (pasted)

Is this possible and if so how I would begin to put it together? I don't know if it's relevant but "Date_Thru" is always in the format of YYYYMMDD, and "Type" is always a "B" or a "C", and when I paste, it's always as a value because the 'Converter' wkbk has a ton formulas.

RLCornish

 

[mumps]

Do you want the value that you enter for "Date_Thru" to be the same in column A for all the rows you added and "Type" to be the same in column B for all the rows?

 

[RLCornish]

Yes, that's precisely right. Col A is 'Date_Thru' and Col B is 'Type', and the info I provide should be populated in these columns relative to the rows just added.

 

[mumps]

After you paste the data, run this macro:

Sub AddData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim dateThru As String
    Dim sType As String
    dateThru = InputBox("Enter the 'Date_Thru' value.")
    sType = InputBox("Enter the'Type'.")
    Range("A2:A" & LastRow) = dateThru
    Range("B2:B" & LastRow) = sType
    Application.ScreenUpdating = True
End Sub

 

[RLCornish]

It definitely gets me a whole lot closer than I was. When I add new data to this workbook, I'm not replacing what's already there, just finding the first blank row and adding the new records. Therefore, I was hoping I could get the 'Date_Thru' & 'Type' to only populate next to the rows I just added. For example...

• I paste new records in D25:Y40
• I run the macro & type in the 'Date_Thru' & 'Type'
• The 'Date_Thru' populates in A25:A40
• The 'Type' populates in B25:B40

Is it possible to limit the macro like this? FYI, prior to me adding the records to D25:Y40, all columns for these rows are blank, so A:B are still blank once I add the records.

RLCornish

 

[mumps]

How about this:

Sub AddData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim bottomA As Long
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim bottomB As Long
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim dateThru As String
    Dim sType As String
    dateThru = InputBox("Enter the 'Date_Thru' value.")
    sType = InputBox("Enter the'Type'.")
    Range("A" & bottomA + 1 & ":A" & LastRow) = dateThru
    Range("B" & bottomB + 1 & ":B" & LastRow) = sType
    Application.ScreenUpdating = True
End Sub

 

[RLCornish]

First, thank you, this is really amazing. I just have a quick question. Is there a way to stop the population at the bottom of the rows I added? The sheet has formulas and such formatted down to row 9,000 so I have plenty of lines for the upcoming updates. When I ran the macro it populated all the way down the bottom of the formated rows.

 

[mumps]

In the rows where you have formulas and that don't yet contain pasted data, what value is returned by the formulas in those cells?

 

[RLCornish]

Hi mumps, just wanted to let you know I figured it out. Here's the code I ended up using... The only change I needed to make was to stop the report at the LastUsedRow and I found that nifty little bit of coding in another macro I was working with.

Sub Date_Thru_and_Type()
'
'Prompts an Input box asking for Date_Thur and Type
'Then it auto-populates that data in A and B respectively for all rows
' with data that do not have info in A or B
'
'
Application.ScreenUpdating = False
Dim LastUsedRow As Long
LastUsedRow = Cells(Rows.Count, "D").End(xlUp).Row
Dim bottomA As Long
bottomA = Range("A" & Rows.Count).End(xlUp).Row
Dim bottomB As Long
bottomB = Range("B" & Rows.Count).End(xlUp).Row
Dim dateThru As String
Dim sType As String
dateThru = InputBox("Enter the 'Date_Thru' value.")
sType = InputBox("Enter the'Type'.")
Range("A" & bottomA + 1 & ":A" & LastUsedRow) = dateThru
Range("B" & bottomB + 1 & ":B" & LastUsedRow) = sType
Application.ScreenUpdating = True
End Sub


Again, thank you for your help. I really would have been nowehre without it.

RLCornish

 

[mumps]

My pleasure.