Consulting

Results 1 to 15 of 15

Thread: Solved By Xld Hit A Snag

  1. #1
    VBAX Contributor
    Joined
    Aug 2007
    Posts
    188
    Location

    Solved By Xld Hit A Snag

    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.

    [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: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
    [/VBA]

    Many Thanks

    Sooty 8

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Contributor
    Joined
    Aug 2007
    Posts
    188
    Location

    Solved By Xld Hit A Snag

    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

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Sorry, how does that data relate to your code, I am not seeing it?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Contributor
    Joined
    Aug 2007
    Posts
    188
    Location

    Solved By Xld Hit A Snag

    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

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    [VBA]
    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
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    VBAX Contributor
    Joined
    Aug 2007
    Posts
    188
    Location

    Solved By Xld Hit A Snag

    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

  9. #9
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Please itemise in steps what you do.
    eg
    Step 1-Enter ID
    Step 2-???
    Step 3-???
    etc.
    Return to step 1
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  10. #10
    VBAX Contributor
    Joined
    Aug 2007
    Posts
    188
    Location
    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

  11. #11
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [VBA]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
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  12. #12
    VBAX Contributor
    Joined
    Aug 2007
    Posts
    188
    Location
    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

  13. #13
    VBAX Contributor
    Joined
    Aug 2007
    Posts
    188
    Location

    Solved By Xld Hit A Snag

    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.

  14. #14
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I think the basic issue is the space at the end of ID No data
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  15. #15
    VBAX Contributor
    Joined
    Aug 2007
    Posts
    188
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •