Updating a list 1

[Wray69]

Let me start off by saying I have ZERO experience with writing VB. I have an EXCEL spreadsheet that I do and INDEX/MATCH in a list. I know need to create a buttton that if it is not in the list I can click and add it to the list and then resort the list. Any help in this matter would be greatly appreciated.

Regards,

Wray

 

[SkipVought]

Wray,

Here's what I would suggest. Try using that features built into Excel and when you are satisfied with your procedure, then turn on the macro recorder and record what you do to add a record.

Some of the feature that you could use are...

1. FIND
2. Data/Form...
3. Data/Sort

Now unless this is a "rush job", you will get alot more out of it, and learn something about VBA, by starting this way. You can come back with the code you put together and get some help with making it work better.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Wray69]

Hey Skip,

That is what I actually tried first, since I do know how to record a MACRO, but I could not figure out how to get my cell value to appear in the find or form fields. So I was stuck at that point.

Regards,

Wray

 

[SkipVought]

Wray,

Need to know something about your sheet and what you are tyring to do.

You could enter a value in a designated cell and use that value for your lookup...

Sub AddARow()
  If Is Error(Application.Match(Range("InputCell"),Range("LookupRange"),0)) Then
   'ya gotta add this record
  Else
   'this one already exists
  End If
End Sub

You could "fire" this in the Worksheet_Change event...

Private Sub Worksheet_Change(ByVal Target As Range)
    Set rng = Application.Intersect(Target, Range("InputCell"))
    If Not rng Is Nothing Then
       AddARow
    End If
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

If you are doing what I think that you are doing, then this might be a slightly better way...

Sub AddARow()
  If IsError(Application.Match(Range("InputCell"), Range("LookupRange"), 0)) Then
    With Range("LookupRange").End(xlDown).Offset(1, 0)
        .Value = Range("InputCell")
        .Offset(0, 1).Select
    End With
  Else
   'this one already exists
  End If
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

If you are doing what I think that you are doing, then this might be a slightly better way...

Sub AddARow()
  If IsError(Application.Match(Range("InputCell"), Range("LookupRange"), 0)) Then
    With Range("LookupRange").End(xlDown).Offset(1, 0)
        .Value = Range("InputCell")
        .Offset(0, 1).Select
    End With
  Else
   'this one already exists
  End If
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Wray69]

Thanks for the help so far Skip. What I have is a list of part #'s Im entering a new part # into a cell, I then have a function telling me if its on the list or not. If it is not I need to be able to add it and then re-sort it.

Wray

 

[SkipVought]

Then use this code

Sub AddARow()
    Application.DisplayAlerts = False
    If IsError(Application.Match(Range("InputCell"), Range("PartNumber"), 0)) Then
      With Range("PartNumber").End(xlDown).Offset(1, 0)
          .Value = Range("InputCell")
          .Sort key1:=Range("PartNumber"), order1:=xlAscending, header:=xlYes
          .CurrentRegion.CreateNames _
              Top:=True, _
              Left:=False, _
              Bottom:=False, _
              Right:=False
      End With
    Else
     'this one already exists
    End If
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Wray69]

Thanks Skip,

Works Beautifully, I really appreciate it. And I understand all most all of it except for what this does
Top:=True, _
Left:=False, _
Bottom:=False, _
Right:=False

Thanks Again.

Regards,

Wray

 

[SkipVought]

Range("PartNumber").End(xlDown).Offset(1, 0)
.CurrentRegion.CreateNames _
              Top:=True, _
              Left:=False, _
              Bottom:=False, _
              Right:=False

What this statement does (the equivalent of what I gave you without the WITHs)
1. it assumes that the list of part numbers has a heading value or PartNumber
2. Range("PartNumber&quot is the part number named range
3. End(xlDown) references the LAST part number
4. Offset(1,0) references the cell just below the LAST part number where the NEW Part Number has been programatically placed
5. CurrentRegion references all contiguous cells from the above referenced cell
6. CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False created new named ranges for the Current Region using the names in the TOP row.

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com