Howdy,
I can get this to work quite right. I have built a little system in vba so that I can put in a first name and last name and some data on an "data entry sheet". Then I hit a submit button which creates and entry on the next sheet (the "records sheet"). The records sheet will have hundreds of entries, one for each person, with each entry consisting of about 10 rows to hold the name, some contact information, and that persons data.
The trick is, i need new entries to post to the data sheet in alphabetical order based on a label in column a for each entry. The labels are concatenated strings combining the last name, a comma, then the first name (typical "last name, first name" format: Doe, John). I am comparing the concatenated label on the data entry page ("chknm") with the concatenated labels on the records page ("lstnm") to determine where the new entry should be inserted (row x). here is an example that shows how i tried to do it...
****************************
dim chknm as string
dim lstnm as string
dim irow as long
dim x as long
sheets("data entry").select
chknm = range("B3").value
rows("1:10").select.
selection.copy
sheets("records sheet").
for irow 1 to totalrows
lstnm = cells(irow, 1).value
if chknm > lstnm then goto nextirow
if chknm < lstnm then
x = irow
else
msgbox ("This person has already been entered")
goto cancelentry
end if
nextirow:
next irow
foundx:
cells(x, 1).entirerow.insert shfit:=xldown
cancelentry:
endsub
*******************************
i dont know what vba is comparing when it performs the if chnm < lstnm line of code... if anyone knows where i am going wrong or can explain what this is actually comparing between the two strings... or can suggest another way to do this entirely, i would really apprecaite it as i have been stuck on this for several days.
Thanks!!!
Jake
I can get this to work quite right. I have built a little system in vba so that I can put in a first name and last name and some data on an "data entry sheet". Then I hit a submit button which creates and entry on the next sheet (the "records sheet"). The records sheet will have hundreds of entries, one for each person, with each entry consisting of about 10 rows to hold the name, some contact information, and that persons data.
The trick is, i need new entries to post to the data sheet in alphabetical order based on a label in column a for each entry. The labels are concatenated strings combining the last name, a comma, then the first name (typical "last name, first name" format: Doe, John). I am comparing the concatenated label on the data entry page ("chknm") with the concatenated labels on the records page ("lstnm") to determine where the new entry should be inserted (row x). here is an example that shows how i tried to do it...
****************************
dim chknm as string
dim lstnm as string
dim irow as long
dim x as long
sheets("data entry").select
chknm = range("B3").value
rows("1:10").select.
selection.copy
sheets("records sheet").
for irow 1 to totalrows
lstnm = cells(irow, 1).value
if chknm > lstnm then goto nextirow
if chknm < lstnm then
x = irow
else
msgbox ("This person has already been entered")
goto cancelentry
end if
nextirow:
next irow
foundx:
cells(x, 1).entirerow.insert shfit:=xldown
cancelentry:
endsub
*******************************
i dont know what vba is comparing when it performs the if chnm < lstnm line of code... if anyone knows where i am going wrong or can explain what this is actually comparing between the two strings... or can suggest another way to do this entirely, i would really apprecaite it as i have been stuck on this for several days.
Thanks!!!
Jake
