Solved: CSV To Sheets In Workbook Using ID Number

[sooty8]

Hi

I receive info via mail formatted as CSV - a macro then runs and separates it as Text to Columns - then have to copy & paste into the sheets as per example on the attached file. Is it possible to read the ID number and place the required data in the correct places within the sheets as per the example on sheets macc & milt. In the full workbook there are 25 sheets all with exactly the same format with different sheet names, however the ID numbers are all different, sometimes they will be repeated on different sheets and still need the data inserted.

Any help much appreciated

Sooty8

[Bob Phillips]

You haven't explained what it is that determines what data goes on which sheet.

What is happening in Blandford?

[sooty8]

Hi Xld

Blimey that was a fast reply "thank you" - the data required is the Miles & Yards all related to the ID number in the sheets in the attached file I copied & pasted the Miles & Yards as an example - Blandford is just one of the sites - I was hoping that when the ID number was found it would be possible to insert the Miles & Yards in the relevant column - now I'm really in a mess I don't know how to explain it any better. You are the top man any idea's?

Regards

Sooty8

[Bob Phillips]

You might understad what you said, but it meant nothing to me.

For instance, why is WE1206 copit to Macc and not to Milt.

[sooty8]

Hi Xld

Not copied because WE1206 will not be on that sheet - have done a userform perhaps explains better have put we1206 on the both sheets this time but it will not be on any other sheets. will attach new file - need to get data from CSV sheet if possible to fill textboxes on UserForm.



Regards

Sooty 8

[sooty8]

Hi Xld

Attached New File Forgot

Regards

Sooty8

[Bob Phillips]

Now, I am totally lost. That has only served to confuse me more, I have absolutely no idea what the code should do.

[arkusM]

Are you saying that you want the "Id#"'s in Column B (WE2763) to be the determinate an which TAB the data goes to. Do the ID only exsist on one tab?

Then search the for the site "Blanford".
Then enter the distances.? If that right?

You would need to loop through with .finds() I think.

[sooty8]

Hi Arkus

Can I come back to this later ? - yesterday was a lousy day in a hospital ward for specialist treatment - going to do a revamp and start again - I know what I want to happen when the data is found hopefully I can explain it better next time.

Many Thanks

Sooty8

[sooty8]

Hi Guys

Done a revamp and attached file - hope this explains it better. Have hard coded off the CSV sheet using the ID No. WE1206 it will work with any ID No. however the info in the textboxes remains the same enter the ID No in the textbox and click FIND DATA and the textboxes fill - click ENTER DATA and it will enter the data on the Macc sheet all in the right columns. What I require is on entering the ID No. and clicking FIND DATA it will search Column "I" on the CSV sheet and fill all the textboxes with the data that it finds against that particular ID No. I can then enter the data on the Macc sheet. Does this explain things??

Many Thanks Sooty8

[Bob Phillips]

[vba]

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:I155)=""" & Tb1A.Text & """,ROW(I2:I155))")
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)
End Select
End If
Next i
End If
End With
End Sub
[/vba]

[sooty8]

Hi Xld

Brilliant and many thanks - I apologise for earlier posts couldn't get my head round it Thursday probably to much going on in my head about the stuff they were going to shove inside me on Friday.

Your help much appreciated

Sooty 8

[Bob Phillips]

I am glad it is sorted now, and I hope that you are well, that whatever they have pumped into you will have its desired effect.

I am intrigued at your business, Blandford is just down the road from me, Cherbourg is directly opposite.

BTW, I am not happy with the static nature of this code, hard-coding the codes in the procedure. It would be far better to tag the textboxes in some way so that the code could look for that tag and allocate dynamically.

[arkusM]

Hi Arkus

Can I come back to this later ? - yesterday was a lousy day in a hospital ward for specialist treatment - going to do a revamp and start again - I know what I want to happen when the data is found hopefully I can explain it better next time.

Many Thanks

Sooty8

No worries. I hope everything went alright.

[sooty8]

Hi Xld

Since joining VBA express I have wondered many times what was the real name behind Xld after your last post I think I've cracked it - a few years back Man Utd were playing Arsenal in the FA Cup final and on another forum that weekend you solved a problem for me and afterwards we shared a few posts about the match - the Gooners were lucky that day ??? fortunately MUtd have gone on winning trophies season after season ( sorry to rub that in) anyway if you remember the problem was all about racing pigeons and working out velocities - birds are liberated from the sites and Blandford is one of them that is why I need the Miles & Yards - the program I use has been developed by me and its a mish / mash of code which is allover the place but it works - I would like to post it on here and ask for help to get rid of the useless bits that are not really required but there is to much private data included within it and removing it is not really an option. I'm 69 later this year in bad health and I do it because in my mispent youth as a member of gang of youngsters we raced pigeons down the back entries of Manchester and if you've done that the love of the birds never leaves you - can't race or keep them anymore but I can give a bit back to the sport in an admin capacity and it keeps the old brain box ticking over The website:
www.pigeonsglossop.co.uk will explain. If you can show me the difference between Hard Code & Dynamically I will try to understand what you mean and the difference it makes.

Regards & Thanks For All Your Help Over The Years " Its Been A Pleasure "

Sooty 8

[Bob Phillips]

Oh no, not another MUner

I do recall something about that previous discussion, not all the details, but it certainly rings some bells. Those birds certainly fly a long way don't they, all the way to Saintes?

As regards hard-coding, you will see some lines like so

[vba]

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)
[/vba]

where the code is part of the select statement and it explicitly states what text boxes are updated - in other words all that info is hard-coded, if you add a new code, or change the form layout, you have top change the code. That is best avoided if possible, which you would do with something like

[vba]

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

If vecRows(i, 1) Then

iRow = vecRows(i, 1)

Me.Controls(some id aligned to the code) = ws.Cells(iRow, 10)
Me.Controls(some similar id aligned to the code) = ws.Cells(iRow, 11)
End If
Next i
[/vba]

Of course the trick is to find something to identify the code within the form design. I was thinking of the textbox name property, whereby you would assign names of Tb4161_1 and Tb4161_2 instead of Tb5 and Tb6, Tb4021_1 and Tb4021_2 instead of Tb7 and Tb8, etc.. The code would then be

[vba]

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

If vecRows(i, 1) Then

iRow = vecRows(i, 1)

Me.Controls(ws.Cells(iRow, "H").Text & "_1") = ws.Cells(iRow, 10)
Me.Controls(ws.Cells(iRow, "H").Text & "_2") = ws.Cells(iRow, 11)
End If
Next i
[/vba]

Then you can add codes and textboxes, change the codes and so on without changing the program, just change the form design.

[sooty8]

Hi Xld

Been an MU supporter since the first match I went to with my father in 1948 only gave up my season ticket 3 years ago - need a favour this afternoon the Gooners could do us a favour by beating Chelsea. Saintes can be flown on the day if the liberation time is early morning - last year in one inland race from FROME the winners were doing 1953 yards per minute over the distance -- mind you there was a south wind behind them.

Over the next couple of days will look at Hard against Dynamically and see if I can get it into my head -- once again many thanks

Regards Sooty8.

[sooty8]

Hi Xld

Today I sent for a CSV file and received it back there were 500 rows of data which is the maximum I would ever send for I altered the sheet "I" range values and it all worked OK - the snag I hit was remembering the last ID No. I had entered - is it possible for the cells on the sheet to change colour say Red when I click on the Enter Data button on the UserForm the code for the Enter Data below -

[vba]
Private Sub enterdata_Click()
Application.ScreenUpdating = False
shIndex = 1
SearchForValue
Application.ScreenUpdating = True
End Sub
[/vba]

All the ID No's are on Column "I" and then on closing the UserForm the cells on the sheet all go back to No Fill.

Regards

Sooty 8

[Bob Phillips]

Not really sure I understand what you are asking.

[sooty8]

Hi Xld

Not very good at explaining things as usual - basically whats happening is I'm forgetting the last ID No. I've entered off the "CSV" sheet - because the Userform is not very large I can see Column "I" behind the UserForm so if I enter WE1206 in Tb1A after clicking Enter Data the cell on the CSV sheet in Column "I" refering to WE1206 would change colour - I would then know the next ID No. to enter. Is that a better explanation?

Regards

Sooty 8