Help with sorting issue??

M

Mekinnik

I have the following code to sort column b in worksheet1 based on name which
works just fine however when it sorts it does not keep all the cells
together, how do I make it keep the cell together then sort column b?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2:B5001"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value =
WorksheetFunction.Max(Range("A1:A5001")) + 1
Me.Range("A:G").Sort key1:=Me.Range("B3"), header:=xlYes
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub
 
D

Dave Peterson

It sure looks like the rows in columns A:G would be kept together.

Any chance you have formulas like this:
=sheet2!x272
in any of the cells that "don't stay together".

Those formulas won't work the way you want when you sort.
 
M

Mekinnik

Thank you for the reply, but it works just fine the problem is created when I
try to apply the following code, I would like to know how to write it as a
formula to enter into all the cells in one column, what I am trying to do is
have the cell change what the code is entering into it to another value.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 5 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Lists").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Lists").Range("StateName"), 0), 0)
Application.EnableEvents = True
End If
End Sub
 
M

Mekinnik

Mr. Peterson,
I would like to know if I could e-mail you what I am working on you better
help you understand what my problem is?
 
D

Dave Peterson

Please keep the discussion in the newsgroup. You'll have lots more potential
helpers by keeping it here.

Try explaining what you want in plain text (no attachments) and if anyone can
help, I'm sure they'll jump in.
 
M

Mekinnik

I have a userform with 5 textboxes and 1 combobox (gets it rowsource from
sheet2 column a) and when the user fills in and selects the state name from
the combobox and then clicks the add button all the information is enter into
sheet1 the next empty row, now two problems with my code.

#1) I want a code or formula that will change the state name when it is
entered into the corresponding column into the state abbreviation (which I
was able to do when I made the column a dropdown box, however I donnot know
how to do it with code.

#2) The code I have listed previously is suppose to sort the manufacturer
name when the worksheet is changed which it does however it will not keep the
other cells in the row together.

I hope I have explained my dilema well enough.

If not please let me know and I will try to elaborate
 
D

Dave Peterson

#1. In the code that you use to add the data from the userform into the
worksheet:

Dim res as variant
dim StateName as String
res = application.vlookup(me.textbox99.value, _
worksheets("Sheet999").range("a:b"), 2, false)
if iserror(res) then
statename = "Not found"
else
statename = res
end if
And put Statename in the cell that gets the spelled out state.

I used textbox99 to hold the abbreviation for the state.
Sheet999 held a table with the abbreviations in column A and the long name in
column B.

#2. If you're updating the worksheet through the userform, then don't rely on
the worksheet_change to sort your data--just sort it after you finish populating
the worksheet (with the userform code).

If you really, really want to sort the data with the worksheet_change event,
then I bet you're changing something that causes the event fire prematurely
(updating the worksheet cell by cell).

Turn off events
update all the cells except column B.
then on events and update the value in column B.
Then your worksheet event will notice the change in column B and sort everything
ok.


with worksheets("somesheethere")
application.enableevents = false
.cells(somerow,"A").value = me.textbox1.value
.cells(somerow,"C").value = me.textbox88.value
.cells(somerow,"D").value = statename
.cells(somerow,"E").value = me.textbox11.value
application.enableevents = true
'the sort will fire with this line.
.cells(somerow,"B").value = me.textbox9999.value
end with


I have a userform with 5 textboxes and 1 combobox (gets it rowsource from
sheet2 column a) and when the user fills in and selects the state name from
the combobox and then clicks the add button all the information is enter into
sheet1 the next empty row, now two problems with my code.

#1) I want a code or formula that will change the state name when it is
entered into the corresponding column into the state abbreviation (which I
was able to do when I made the column a dropdown box, however I donnot know
how to do it with code.

#2) The code I have listed previously is suppose to sort the manufacturer
name when the worksheet is changed which it does however it will not keep the
other cells in the row together.

I hope I have explained my dilema well enough.

If not please let me know and I will try to elaborate
Click to expand...
 
M

Mekinnik

Mr. Peterson I followed what you posted but it doesn't work still maybe I'm
doing some thing wrong. I have the worksheets 'MANCODE' where the userform is
adding info, 'Lists' where I have column A the state abbreviations and cloumn
B the full stste names. Here is what my code looks like for the 'ADD' button
click event.

Private Sub BtnAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim res As Variant
Dim StateName As String
res = Application.VLookup(Me.CmbSt.Value, _
Worksheets("Lists").Range("A:B"), 2, False)
Set ws = Worksheets("MANCODE")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for the manufacturer name
If Trim(Me.TxtMan.Value) = "" Then
Me.TxtMan.SetFocus
MsgBox "Please enter the Manufacturer's name"
Exit Sub
End If
If IsError(res) Then
StateName = "Not Found"
Else
StateName = res
End If

'copy the data to the database
Application.EnableEvents = False
ws.Cells(iRow, 3).Value = Me.TxtAdd.Value
ws.Cells(iRow, 4).Value = Me.TxtCity.Value
ws.Cells(iRow, 5).Value = Me.CmbSt.Value
ws.Cells(iRow, 6).Value = Me.TxtZip.Value
ws.Cells(iRow, 7).Value = Me.TxtPhn.Value
Application.EnableEvents = True

'the sort will fire with this line.
ws.Cells(iRow, 2).Value = Me.TxtMan.Value

'clear the data
Me.TxtMan.Value = ""
Me.TxtAdd.Value = ""
Me.TxtCity.Value = ""
Me.CmbSt.Value = ""
Me.TxtZip.Value = ""
Me.TxtPhn.Value = ""

End Sub

I believe after rereading your post again that it is backwards I want when
the user selects the full state name from 'CmbSt' combobox it will return the
state abbreviation back to 'MANCODE' column E and with the code the way it is
column E gets the full state name not the abbreviation.
 
D

Dave Peterson

So the only part that doesn't work is the abbreviation/long state name?

with worksheets("lists")
res = application.match(me.cmbst.value,.range("B:b",0)
if iserror(res) then
'no match found!
else
statename = .range("A:a")(res)
end if
end with

if there was more that didn't work, you're going to have to describe that part.

Mr. Peterson I followed what you posted but it doesn't work still maybe I'm
doing some thing wrong. I have the worksheets 'MANCODE' where the userform is
adding info, 'Lists' where I have column A the state abbreviations and cloumn
B the full stste names. Here is what my code looks like for the 'ADD' button
click event.

Private Sub BtnAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim res As Variant
Dim StateName As String
res = Application.VLookup(Me.CmbSt.Value, _
Worksheets("Lists").Range("A:B"), 2, False)
Set ws = Worksheets("MANCODE")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for the manufacturer name
If Trim(Me.TxtMan.Value) = "" Then
Me.TxtMan.SetFocus
MsgBox "Please enter the Manufacturer's name"
Exit Sub
End If
If IsError(res) Then
StateName = "Not Found"
Else
StateName = res
End If

'copy the data to the database
Application.EnableEvents = False
ws.Cells(iRow, 3).Value = Me.TxtAdd.Value
ws.Cells(iRow, 4).Value = Me.TxtCity.Value
ws.Cells(iRow, 5).Value = Me.CmbSt.Value
ws.Cells(iRow, 6).Value = Me.TxtZip.Value
ws.Cells(iRow, 7).Value = Me.TxtPhn.Value
Application.EnableEvents = True

'the sort will fire with this line.
ws.Cells(iRow, 2).Value = Me.TxtMan.Value

'clear the data
Me.TxtMan.Value = ""
Me.TxtAdd.Value = ""
Me.TxtCity.Value = ""
Me.CmbSt.Value = ""
Me.TxtZip.Value = ""
Me.TxtPhn.Value = ""

End Sub

I believe after rereading your post again that it is backwards I want when
the user selects the full state name from 'CmbSt' combobox it will return the
state abbreviation back to 'MANCODE' column E and with the code the way it is
column E gets the full state name not the abbreviation.
Click to expand...
 
M

Mekinnik

I tried the code below and it didn't work, then I tried it differently and it
still didn't work, any suggestions?

Private Sub BtnAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim res As Variant
Dim StateName As String

Set ws = Worksheets("MANCODE")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for the manufacturer name
If Trim(Me.TxtMan.Value) = "" Then
Me.TxtMan.SetFocus
MsgBox "Please enter the Manufacturer's name"
Exit Sub
End If
With Worksheets("Lists")
res = Application.VLookup(Me.CmbSt.Value, _
Worksheets("Lists").Range("A:B"), 2, False)
'res = Application.Match(Me.CmbSt.Value, .Range("B:b"), 0)
'If IsError(res) Then
'StateName = "Not Found"

StateName = .Range("A:a")(res)
End With

'copy the data to the database
Application.EnableEvents = False
ws.Cells(iRow, 3).Value = Me.TxtAdd.Value
ws.Cells(iRow, 4).Value = Me.TxtCity.Value
ws.Cells(iRow, 5).Value = Me.CmbSt.Value
ws.Cells(iRow, 6).Value = Me.TxtZip.Value
ws.Cells(iRow, 7).Value = Me.TxtPhn.Value
Application.EnableEvents = True

'the sort will fire with this line.
ws.Cells(iRow, 2).Value = Me.TxtMan.Value

'clear the data
Me.TxtMan.Value = ""
Me.TxtAdd.Value = ""
Me.TxtCity.Value = ""
Me.CmbSt.Value = ""
Me.TxtZip.Value = ""
Me.TxtPhn.Value = ""

End Sub
 
M

Mekinnik

I managed to get it to work with some tweaking here is the modified code,
thank you for all your help.

Private Sub BtnAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim res As Variant
Set ws = Worksheets("MANCODE")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for the manufacturer name
If Trim(Me.TxtMan.Value) = "" Then
Me.TxtMan.SetFocus
MsgBox "Please enter the Manufacturer's name"
Exit Sub
End If

'find and copy state abbreviation to row 5
With Worksheets("Lists")
res = Application.VLookup(Me.CmbSt.Value, _
Worksheets("Lists").Range("A:B"), 2, False)
If IsError(res) Then
Else
ws.Cells(iRow, 5).Value = (res)
End If
End With

'copy the data to the database
Application.EnableEvents = False
ws.Cells(iRow, 3).Value = Me.TxtAdd.Value
ws.Cells(iRow, 4).Value = Me.TxtCity.Value
ws.Cells(iRow, 6).Value = Me.TxtZip.Value
ws.Cells(iRow, 7).Value = Me.TxtPhn.Value
Application.EnableEvents = True

'the sort will fire with this line.
ws.Cells(iRow, 2).Value = Me.TxtMan.Value

'clear the data
Me.TxtMan.Value = ""
Me.TxtAdd.Value = ""
Me.TxtCity.Value = ""
Me.CmbSt.Value = ""
Me.TxtZip.Value = ""
Me.TxtPhn.Value = ""

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with combining/merging code 1
Help with this code 9
Help with code issues 2
VBA for data validation in 3rd column does not run. I need help. 1
sorting automatically 2
Value issue 7
CORRECTION NEEDED 2
NEED ADDITION IN MACRO 0

Top