PDA

View Full Version : Insert row with criteria



UncleBob
10-16-2008, 05:16 AM
Hi there,
I'm a complete novice so please be nice!

What I have is:
Two blocks of data, 1st block is A5:AG103 named "VolAndVal", 2nd block is A130:N227 named "PriceBlock".
The 1st row in each block are descriptions with the data starting underneath (rows 6 & 131).
In VolAndVal ColA is Description(text), ColB Is Code(Numeric), ColC is StdPrice(Numeric, 3 decimals) withs Cols D-AG formulas (the block is sorted on code order)
In PriceBlock ColA is Description, ColB Is Code Cols C-N are prices and wiill be inputted later.

What I want is a Macro to insert a new code:
1. Put code into an Inputbox
2. Look at the codes in VolAndVal
3. If Code exists, tell the user (msgbox) and exit
4. Insert a row in the right place & paste the code from the Inputbox
5. Inputbox for Description & paste into ColA
6. Inputbox for StdPrice & paste into ColC
7. Copy the formulas from the row above
8. Insert a row in PriceBlock, copy & paste the Description and Code from above
9. That's it

I know this is probably childs play for you guys but I'm stumped!

Many thanks in advance

Paul

GTO
10-17-2008, 04:53 AM
Greetings UncleBob,

No worries as to folks here being nice. I have watched threads wherein the "answerer" has expent a great deal of effort in guiding and helping the "questioner" in reaching a solution. Quite often, I have seen where this goes as far as guiding someone nearly through their entire project!

From what I can tell, "Code" may be a part number or similar.

When you say you want the macro to "put code into an InputBox", you may be saying that the user would enter a code in the InputBox and the macro would do the rest. Would it be possible for you to post an example workbook, so we could see more clearly as to what you are trying to do?

Mark

UncleBob
10-17-2008, 06:15 AM
Hi Mark,

Thanks for answering.

"you may be saying that the user would enter a code in the InputBox and the macro would do the rest" - that's the whole idea!

I've attached a slimmed down copy of the file as you suggested - please note the column "StdPrice" has now been deleted.

I hope this helps

Many thanks

Paul

GTO
10-17-2008, 10:35 PM
Hello Paul,

While certainly only one way of doing this, here's one way to insert rows.

I did note that the formulas don't copy down quite correctly, due in part to some absolute references in your VLookup's.

For the below:

I changed the codename of Sheet1 to shtRecord.
'AI13': enter the text "End_S1"
'AI24': ..."End_S2"

I would then hide column AI.

Hope this helps,

Mark


Option Explicit
Sub NewRecord_Insert()
Dim lngRow As Long, _
lngRow_SectEnd_1 As Long, _
lngRow_SectEnd_2 As Long, _
lngCode As Long, _
strDesc As String
Do While lngRow_SectEnd_1 = 0
'// As we loop thru rows, keep the val of lngRow the same as the row we're //
'// currently looking at. //
lngRow = lngRow + 1
'// Starting at [AI1], look from row 1 until we find our marker ("End_S1"). //
If shtRecords.Cells(lngRow, 35).Text = "End_S1" Then
'// When we find our marker, assign the row number //
lngRow_SectEnd_1 = lngRow - 1
Exit Do
'// NOTE: There are several ways to find a 'marker' or where a range ends. //
'// Since I chose a marker, in case it somehow gets deleted, set the number //
'// to something higher than you'd expect to get to record-wise... //
ElseIf lngRow > 1000 Then
MsgBox "The search marker has been deleted. Please contact XXXX", _
vbCritical, "ERROR"
Exit Sub
End If
Loop
'// SAA for finding the end of the second section. //
Do While lngRow_SectEnd_2 = 0
lngRow = lngRow + 1
If shtRecords.Cells(lngRow, 35).Text = "End_S2" Then
lngRow_SectEnd_2 = lngRow
Exit Do
ElseIf lngRow >= 2000 Then
MsgBox "The search marker has been deleted. Please contact XXXX", _
vbCritical, "ERROR"
Exit Sub
End If
Loop
'// Using the Application.Input box allows us to return something other than a //
'// string; in this case a number. Default is optional, I just threw it in if //
'// the next new item is likely to be numbered one higher than the highest //
'// currently existing. //
lngCode = Application.InputBox( _
Prompt:="What is the new item's code? (Like: ""100041"")", _
Title:="New Item Code Required", _
Default:=Application.WorksheetFunction _
.Max(shtRecords.Range(Cells(6, 2), _
Cells(lngRow_SectEnd_1, 2))) + 1, _
Type:=1)
strDesc = Application.InputBox(Prompt:="What is the new item's description?", _
Title:="New Item Description Required", _
Type:=2)
'// Before we commit to adding rows, let's ensure the user put something in both//
'// boxes... //
If Not lngCode = 0 _
And Not strDesc = Empty Then
With shtRecords
.Rows(lngRow_SectEnd_1 & ":" & lngRow_SectEnd_1).Insert Shift:=xlDown
.Rows(lngRow_SectEnd_2 & ":" & lngRow_SectEnd_2).Insert Shift:=xlDown
.Range(Cells(lngRow_SectEnd_1 - 1, 15), Cells(lngRow_SectEnd_1 - 1, 33)) _
.Copy Destination:= _
shtRecords.Range(Cells(lngRow_SectEnd_1, 15), Cells(lngRow_SectEnd_1, 33))
.Cells(lngRow_SectEnd_1, 1).Value = strDesc
.Cells(lngRow_SectEnd_1, 2).Value = lngCode

.Cells(lngRow_SectEnd_2, 1).Value = strDesc
.Cells(lngRow_SectEnd_2, 2).Value = lngCode
End With

Else
'// Whatever appropriate msg to let the user know he missed a step. //
MsgBox "Helloooooo! There was only two steps. Do you want pictures?", _
vbExclamation, ""
End If
End Sub

UncleBob
10-20-2008, 06:23 AM
Mark, You are a scholar and a gentleman!
I'll give this a try later this week.

Many thanks

Paul

GTO
10-20-2008, 07:30 AM
Well thank you and hope it works for ya; happy to help. :friends:

Mark