Gods of VB, I call on thy.
Right, what I gave you was an example of the approach, not The
Solution.
You can make the "result" formulas go down 2000 rows or more if you
want (whatever the max might ever be), and the results will only
display as far as there is data for that station (which I assumed you
were using "name" as a surrogate for).
As far as the amount of data changing daily on the Data tab, you can
make the match formulas look up the whole way down the column from 2
through 65536. Or look up how to create dynamic named ranges and use
those to account for the varied amount of input data.
On Nov 12, 2:13 pm, pgarcia wrote:
Thank for your effort, but it did not work. The new data does not have
employee names but station codes, e.g. LAX, GGC, JFK ect. The data is from
D2:W2 (22 columns of data) and has around 2879 rows, which changes on a daily
bases. What Faisal has given me, I thought it could be modified to lookup the
station code (I'm thinking, yes, an input cell) and return the 22 lines of
data, where LAX may be repeated 28 to 35 times (in today's data LAX is listed
282 times). Since it changes daily, I don't want to worry about copying a
formula down to a certain cell or row.
Thank you
" wrote:
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"
...
read more »
|