Solved By Xld Hit A Snag

[sooty8]

Hi

Below is the code that solved my original problem - have added another line for the Case.5087 and added 2 text boxes to cover and fill the sheets via the ID number - and with each individual ID it works spot on - the problem is I have now received the csv file for the 400 or so ID's for Case.5087 it means I would have to enter each ID number for Case 5087 is there a way of reading the sheet "CSV" and adding them enbloc. Case.5087 in red.


Private Sub Find_Click()
Dim ws As Worksheet
Dim vecRows As Variant
Dim iRow As Long
Dim i As Long

Set ws = Worksheets("CSV")

With Me

vecRows = Application.Evaluate("IF(TRIM(I2:I500)=""" & Tb1A.Text & """,ROW(I2:I500))")
If .Tb1A.Text <> "" Then

For i = LBound(vecRows) To UBound(vecRows)

If vecRows(i, 1) Then

iRow = vecRows(i, 1)

Select Case ws.Cells(iRow, "H").Value2

Case 4161: .Tb5.Text = ws.Cells(iRow, 10): .Tb6.Text = ws.Cells(iRow, 11)
Case 4021: .Tb7.Text = ws.Cells(iRow, 10): .Tb8.Text = ws.Cells(iRow, 11)
Case 5011: .Tb9.Text = ws.Cells(iRow, 10): .Tb10.Text = ws.Cells(iRow, 11)
Case 4180: .Tb11.Text = ws.Cells(iRow, 10): .Tb12.Text = ws.Cells(iRow, 11)
Case 4048: .Tb13.Text = ws.Cells(iRow, 10): .TB14.Text = ws.Cells(iRow, 11)
Case 4191: .Tb15.Text = ws.Cells(iRow, 10): .Tb16.Text = ws.Cells(iRow, 11)
Case 5073: .Tb17.Text = ws.Cells(iRow, 10): .Tb18.Text = ws.Cells(iRow, 11)
Case 5029: .Tb19.Text = ws.Cells(iRow, 10): .Tb20.Text = ws.Cells(iRow, 11)
Case 4087: .Tb21.Text = ws.Cells(iRow, 10): .Tb22.Text = ws.Cells(iRow, 11)
Case 5042: .Tb23.Text = ws.Cells(iRow, 10): .Tb24.Text = ws.Cells(iRow, 11)
Case 4133: .Tb25.Text = ws.Cells(iRow, 10): .Tb26.Text = ws.Cells(iRow, 11)
Case 5087: .Tb27.Text = ws.Cells(iRow, 10): .Tb28.Text = ws.Cells(iRow, 11)
End Select
End If
Next i
End If
End With
End Sub


Many Thanks

Sooty 8

[Bob Phillips]

Are you wanting to add just extra lines of VBA?

If so, I would use Excel to generate the code (Autogen). Post the CSV file and I will show you what I mean. I also need to know what tb is associated with which ID.

[sooty8]

Hi Xld

Thanks for fast reply - the tb I use for the ID No. is Tb1A
tried to post a csv sheet upload kept knocking it back have made one up in xls.


Thanks

Sooty8

[Bob Phillips]

Sorry, how does that data relate to your code, I am not seeing it?

[sooty8]

Hi Xld

I can never explain myself properly get all tied up trying - anyway have attached new file with Userform - In which I have to enter each ID No. separately to fill the sheet - this is a very shortened version with just one sheet - all are using the same case.5087 can they be loaded enbloc?

Thanks for your patience

Sooty 8

[Bob Phillips]

I'm still not getting it mate.

Let me tell you what I thought you were asking for.

I thought you had a whole host of extra conditions that you wanted to add to the Select Case statement, and you wanted to avoid typing them all out manually as you had a CSV file that might be used to generate them. If that is correct, I just don't see the correlation between that CSV data and that code.

[mdmackillop]

Can you explain the steps you are using to complete your task. I don't follow what you mean.

BTW, You can loop through sequentially numbered controls as follows

With Me
.Tb2.Text = rngFound.Offset(0, 2).Value
.Tb3.Text = rngFound.Offset(0, 1).Value
.Tb4.Text = rngFound.Offset(0, 4).Value
For i = 5 To 28
Set Ctrl = Controls("Tb" & i)
If Len(Trim(Ctrl.Text)) <> 0 Then _
rngFound.Offset(0, i) = Ctrl.Text
Next
End With

[sooty8]

Hi Guys

I hope for a better explanation leave it is now working by me entering the ID No. and doing it individually. How about adding a commandbutton to the userform that would find all the ID No's on the CSV sheet and then add the Miles in column AB / the Yards in column AC against each correct ID No. found. It will be next week before the big list is sent by email see CSV sheet I've altered - the mail arrives and I paste it into the received column then do text to columns. Is the above feasible??

Regards

Sooty 8

[mdmackillop]

Please itemise in steps what you do.
eg
Step 1-Enter ID
Step 2-???
Step 3-???
etc.
Return to step 1

[sooty8]

Hi Guys
Step 1 - Enter ID Number
Step 2 - Find Data
Step 3 - Enter Data
That puts the Miles & Yards in the correct cells on the Macc Sheet for the ID No. entered
then I have to do it for the next ID No. with 400 or so in one mail next week all referring to code 5087 it will probably take all morning to make sure I enter the correct ID No. for each individual member.

Regards

Sooty 8

[mdmackillop]

Private Sub CommandButton1_Click()
Dim i
For i = 2 To Cells(Rows.Count, "I").End(xlUp).Row
Tb1A = Cells(i, "I")
Find_Click
enterdata_Click
Next
End Sub

[sooty8]

Hi MD

Many thanks for your help - the code does exactly what I wanted it to do just one question - it is possible that the ID No. could be on several different sheets could the code be made to enter the data on all the sheets that it finds that particular ID No.

Once again Thanks for your help

Sooty 8

[sooty8]

Hi Guys

Used the code by MD last week and it worked OK today got the big list and everything seemed to go haywire have shortened the list as in the attached file and tried fort several hours to get it working again no joy - could you please have a look at the attached and see if you can find an answer.

Regards

Sooty 8.

[mdmackillop]

I think the basic issue is the space at the end of ID No data

[sooty8]

Hi Md

Thanks for the info and code - tried & tested it works OK - tomorrow will transfer it all to the big program with all the relevant sheets on - hope I don't make a mess of it?? If I do I'll more than likely be back for more help.

Regards

Sooty8