On Nov 12, 10:23 am, pgarcia
wrote:
Yes. I have list of about 166 stations, LAX, GGV etc. I would like to select
a station from a drop down menu or have the user input it. It they input the
station code then there should be message indication that there was not
match.
Thanks
"Faisal..." wrote:
It's good that it worked out for you. When you mean select name, do
you mean to select a name and do the (same) operation (as in this
code) only for this name?
You will have to trim the code down and maybe use a userform.
Faisal...
On 9 Nov, 19:03, pgarcia wrote:
Hello again.
I think I figered this out. I was a formating issue. This code is very
sensitve. There was a stange space in font of the name. It's fixed and the
code runs great. I did howeve modifyed it a bit. (see comment below the VB
code)
Sub Update_Data()
Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie As Integer
Dim strEmployee As String
' ' ' ' Update the Invoice data on the Employe sheets
i_david = 19
i_betsy = 19
i_tally = 19
i_julio = 19
i_jackie = 19
i_jenni = 19
i = 2
While Sheets("Invoice").Cells(i, 1) < ""
Select Case Sheets("Invoice").Cells(i, 8)
Case "David"
ii = i_david
i_david = i_david + 1
Case "Betsy"
ii = i_betsy
i_betsy = i_betsy + 1
Case "Tally"
ii = i_tally
i_tally = i_tally + 1
Case "Julio"
ii = i_julio
i_julio = i_julio + 1
Case "Jackie"
ii = i_jackie
i_jackie = i_jackie + 1
Case "Jenni"
ii = i_jenni
i_jenni = i_jenni + 1
End Select
strEmployee = Sheets("Invoice").Cells(i, 8).Value
Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1)
Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2)
Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3)
Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4)
Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5)
Sheets(strEmployee).Cells(ii, 6) = Sheets("Invoice").Cells(i, 6)
Sheets(strEmployee).Cells(ii, 7) = Sheets("Invoice").Cells(i, 7)
i = i + 1
Wend
' ' ' ' Update the On Account data on the Employe sheets
i_david = 79
i_betsy = 38
i_tally = 59
i_julio = 59
i_jackie = 79
i_jenni = 47
i = 2
While Sheets("On Account").Cells(i, 1) < ""
Select Case Sheets("On Account").Cells(i, 10)
Case "David"
ii = i_david
i_david = i_david + 1
Case "Betsy"
ii = i_betsy
i_betsy = i_betsy + 1
Case "Tally"
ii = i_tally
i_tally = i_tally + 1
Case "Julio"
ii = i_julio
i_julio = i_julio + 1
Case "Jackie"
ii = i_jackie
i_jackie = i_jackie + 1
Case "Jenni"
ii = i_jenni
i_jenni = i_jenni + 1
End Select
strEmployee = Sheets("On Account").Cells(i, 10).Value
Sheets(strEmployee).Cells(ii, 1) = Sheets("On Account").Cells(i, 1)
Sheets(strEmployee).Cells(ii, 2) = Sheets("On Account").Cells(i, 2)
Sheets(strEmployee).Cells(ii, 3) = Sheets("On Account").Cells(i, 3)
Sheets(strEmployee).Cells(ii, 4) = Sheets("On Account").Cells(i, 4)
Sheets(strEmployee).Cells(ii, 5) = Sheets("On Account").Cells(i, 5)
Sheets(strEmployee).Cells(ii, 6) = Sheets("On Account").Cells(i, 6)
Sheets(strEmployee).Cells(ii, 7) = Sheets("On Account").Cells(i, 7)
Sheets(strEmployee).Cells(ii, 8) = Sheets("On Account").Cells(i, 8)
Sheets(strEmployee).Cells(ii, 9) = Sheets("On Account").Cells(i, 9)
i = i + 1
Wend
End Sub
Thanks, that helped a lot.
Question, could this code be modify to select a name? Meaning. With a drop
down list or inputing the name in a cell, could this return the same info.
This would be for another project.
"Faisal..." wrote:
I think that your data layout must be different from the tables you
have posted. I assumed that employee name would be in the 5th
column.
in the line:
strEmployee = Sheets("Invoice").Cells(i, 5).Value
Update the 5 to another figure referring to the column number of where
the employee name is stored.
Faisal ...
On Nov 8, 8:10 pm, pgarcia wrote:
strEmployee = "ML-500-011107-30"
"Faisal..." wrote:
What is the value of strEmployee? Put the cursor on strEmployee when
it stops.
On Nov 8, 3:47 pm, pgarcia wrote:
Hello,
The VB code stops at the following line:
strEmployee = Sheets("Invoice").Cells(i, 5).Value
Sheets(strEmployee).Cells(ii, 1) = Sheets("Invoice").Cells(i, 1)
<----
Sheets(strEmployee).Cells(ii, 2) = Sheets("Invoice").Cells(i, 2)
Sheets(strEmployee).Cells(ii, 3) = Sheets("Invoice").Cells(i, 3)
Sheets(strEmployee).Cells(ii, 4) = Sheets("Invoice").Cells(i, 4)
Sheets(strEmployee).Cells(ii, 5) = Sheets("Invoice").Cells(i, 5)
Thanks
"Faisal..." wrote:
Assuming that your sheets are named as follows: "Invoice", "On
Account", "David", "Betsy", "Tally", "Julio" "Jackie"
STEP 1: Create a Module and add the following code:
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''***''''''''''''''''''
Sub Update_Data()
Dim i, ii, i_david, i_betsy, i_tally, i_julio, i_jackie as Integer
Dim strEmployee as string
' ' ' ' Get Invoice Data First
' ' ' ' Copy and Paste Header
Sheets("Invoice").Select
Range("A1:A5").Select
Sheets("David").Select
Range("A1").Select
Activesheet.Paste
Sheets("Betsy").Select
Range("A1").Select
Activesheet.Paste
Sheets("Tally").Select
Range("A1").Select
Activesheet.Paste
Sheets("Julio").Select
Range("A1").Select
Activesheet.Paste
Sheets("Jackie").Select
Range("A1").Select
Activesheet.Paste
Sheets("Invoice").Select
' ' ' ' Update the Invoice data on the Employe sheets
i_david=2
i_betsy=2
i_tally=2
i_julio=2
i_jackie=2
i=2
While Sheets("Invoice").Cells(i,1) < ""
Select Case Sheets("Invoice").Cells(i,5)
Case "David"
ii=i_david
i_david=i_david+1
Case "Betsy"
ii=i_betsy
i_betsy=i_betsy+1
Case "Tally"
ii=i_tally
i_tally=i_tally+1
Case "Julio"
ii=i_julio
i_julio=i_julio+1
Case "Jackie"
ii=i_jackie
i_jackie=i_jackie+1
End Select
strEmployee=Sheets("Invoice").Cells(i,5).value
Sheets(strEmployee).Cells(ii,1)=Sheets("Invoice"). Cells(i,1)
Sheets(strEmployee).Cells(ii,2)=Sheets("Invoice"). Cells(i,2)
Sheets(strEmployee).Cells(ii,3)=Sheets("Invoice"). Cells(i,3)
Sheets(strEmployee).Cells(ii,4)=Sheets("Invoice"). Cells(i,4)
Sheets(strEmployee).Cells(ii,5)=Sheets("Invoice"). Cells(i,5)
i=i+1
Wend
' ' ' ' Get On Account Data
' ' ' ' Copy and Paste Header
Sheets("On Account").Select
Range("A1:A5").Select
Sheets("David").Select
Range(Cells(i_david+2,1)).Select
Activesheet.Paste
Sheets("Betsy").Select
Range(Cells(i_betst+2,1)).Select
Activesheet.Paste
Sheets("Tally").Select
Range(Cells(i_tally+2,1)).Select
Activesheet.Paste
Sheets("Julio").Select
Range(Cells(i_julio+2,1)).Select
Activesheet.Paste
Sheets("Jackie").Select
...
read more »
If you have a lot of stations to deal with then I might take a
different approach than the VBA that is hard coded to each employee.
I just did one of these for you, so I'll describe how I set it up
using only formulas and lookups rather than VBA.
'Data' Tab
Row 1 is your headings, A2:E10 is the data you provided.
'Result' tab
A1 contains the name of the employee to lookup (David, for example.)
A2: =IF(ISERROR(MATCH($A$1,OFFSET(Data!$E$2,B1,0,100,1 ),0)),"",MATCH($A
$1,OFFSET(Data!$E$2,B1,0,100,1),0))
B2: =IF(ISERROR(+B1+A2),"",+B1+A2)
C2: =IF(ISERROR(INDEX(Data!A$2:A$10,$B2)),"",INDEX(Dat a!A$2:A$10,$B2))
Copy C2 across to column G and format each column appropriately.
A2 find the first instance of the name AFTER the last time it found
the name. (The OFFSET does that).
Copy-paste row 2 down to whatever the maximum number of rows you are
likely to have for a single employee in a day. The ISERROR portions
will find when the formula "stops finding" David and prevent it from
showing #NAs or #VALUEs.
As for your last question, I think you want to have a pulldown with
the list of stations and the option to override it. Again, it CAN be
accomplished w/o VBA, though it would be more elegant and foolproof
with it. Depending on who wants to use it, make your decision
accordingly.
Set up a pulldown menu with:
Input range = I2:I29 (or whereever you want it and as large as
you need)
Cell link of I1
Put an "override" cell in K1 (where they can type in a name/site
instead of using the pulldown).
In A1 (where "David" was typed in before) put:
=IF(K2="",INDEX(I2:I29,I1),IF(ISNA(MATCH(K2,I2:I29 ,0)),"Station Not
Found--use the Menu or try again",K2))
A small amount of code assigned to the pulldown could be used to wipe
out the override cell when a selection is made with the dropdown.
I think this accomplishes what you're trying to do. I'd be happy to
send along a copy of this in a workbook if you email me.