Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mid & Variable Range (97 SR1)

    Greetings all. Being a bit rusty with my VBA, and having not found a solution in the archives, I throw this question to the floor.
    I need to pull info into two cells. I am sorting through a data sheet, hoping to compile with VBA, information only where there is a value of <>0, in Range(C7,R).
    I need to pull the product from C6, using mid function (no problem), but I also need to pull the customer number from a variable Cell. It is above Range(C4,R), but it can vary from 1 - 4 rows. So how do I select the customer string to get the mid (customer # value) so that in the end, for each line <> 0, Range(C4,R) = Cust, C5=mid of C6 and C7 = Sales? Every combination of declaring, do loops, etc, there is something that just isn't kosher with VBA.

    Your assistance please.

    Thanks.

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    Your references to Range(C4,R) and Range(C7,R) confuses me -- what does that refer to? There's no such thing as range(C4,R), so what do you mean?

  3. #3
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    Sorry - I generally like to keep things simple, so when I define a Range, I use C for Column and R for Row.
    So my data would be found from Column 4 to Column 7 on which ever row had the valid data in it.

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    Sorry -- I don't get it. Maybe a very precise example would help.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    You might not need VBA. Like Bob, I can't understand your setup easily. But, using the Indirect() function and/or the Lookup functions might do it.
    Ruth

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    How are we supposed to know which of those cells has the data in it? Are all of the others empty? Is the data a string or a number?
    Legare Coleman

  7. #7
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    Sorry one and all. It was a bit confusing and it is impractical for me to include a demo. I guess I need to brush up on my VBA, but, getting back to the question, how can I select a variable range and get a mid string. The customer information is in Column 4, in a string (6 digit number + name), with the sales info in Column 7. Basically the problem is that for every valid info in column 7 where the sales <> 0, I need to find the customer info in Column 4. The problem is that the ROW for that information is not on the same row as the data. IE, the sales information could be on row 12, but the Customer information is on row 9. I need to set up a variable to find the valid sales info, then find the customer info in Column 4 and use the mid string to get the customer number and convert it into a value. Does that help?
    If not, please don't worry about it too much. I am just hoping to automate the manualy process.

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mid & Variable Range (97 SR1)

    Your explanation is absolutely no help! As Bob says, we need a precise and short example.

    But, and I'm just guessing here, do you maybe want to use the Cells method instead of the Range method? It has parameters for both row and column.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    Unfortunately, this explanation is not much more help. It sounds like you want to use the .Offset property of the range object to get to the customer information. However, saying that the customer information is not in the same row is not much help in telling us where it is. There are 65,535 other rows where it could be. All I can do is guess what you are trying to do and give you an example that might give you an idea of where to start:

    If the offset from row 1 to the row for the sales info is in a variable named lRow, and the customer information is two rows above that row in column D (the fourth column), then this is how I would get to the customer information:

    <pre> strCustomer = Worksheets("Sheet1").Range("D1").Offset(lRow-2,0)
    </pre>

    Legare Coleman

  10. #10
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    Ok guys. I get the hint. I am too vague. Sorry, I can't leave a live sample, but that is the problem with private sales data. Listed below was the REALLY BAD code that I was trying to work on. Like I said, I am really rusty/inexperienced with VBA. To note, I did end up using some filters and some manual effort to get the job done, but it will be an ongoing project. Having a look at my bastardized code, perhaps you will have an idea of what I was attempting. My apologies for the confusion. Consider this post closed. Thanks again for all your effort anyway.

    Sub Macro1()
    '
    ' Macro1 Macro
    ' R=Row

    R = 1
    Do While Cells(R, 7) < 370
    ' 370 Lines to sort through
    If Cells(R, 7) <> 0 Then
    ' if the Sales volume <> 0, then we want to work on this line
    Cells(R, 5) = Mid(Cells(R, 6), 1, 6)
    ' Pulling product number from product description
    Cells(R, 4).Select
    'Column that has customer information
    mycust = Selection.End(xlUp).Select
    'Select variable row that has customer number & name
    'This is the variable it can be 1 - 7 lines up
    Cells(R, 4) = Mid(mycust, 1, 6)
    'That's the rub, find the string above Cells(R,4) and get the String
    Else
    R = R + 1
    End If
    R = R + 1
    Loop

    End Sub

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    Maybe this is closer to what you want:

    <pre>Sub Macro1()
    '
    ' Macro1 Macro
    ' R=Row

    Dim lR As Long, lCust As Long
    lR = 1
    Do While lR < 370
    ' 370 Lines to sort through
    If Cells(lR, 7) <> 0 Then
    ' if the Sales volume <> 0, then we want to work on this line
    Cells(lR, 5) = Mid(Cells(lR, 6), 1, 6)
    ' Pulling product number from product description
    Cells(lR, 4).Select
    'Column that has customer information
    lCust = Cells(lR, 4).End(xlUp).Row
    'Select variable row that has customer number & name
    'This is the variable it can be 1 - 7 lines up
    Cells(lR, 4) = Mid(Cells(lCust, 4), 1, 6)
    End If
    lR = lR + 1
    Loop
    End Sub
    </pre>

    Legare Coleman

  12. #12
    Lounger
    Join Date
    Jun 2001
    Location
    Burlington, Ontario, Canada
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    Ta Da! I have seen the light. Unfortuneately your code did not quickly solve my dilema, but it did point me (finally) in the direction I needed to go. First, the If statement did not work "<> 0". For some reason, it read all the data, so I would have to assume that the data dump I had to work with was not a true value. I filtered out the 0's to null. Listed below is the final version that worked and wrote what I needed to the lines that needed. Thanks for your insight into my vague problem. You helped me solve it!

    Dim lR As Long, lCust As Long
    lR = 1


    Do While lR < 358

    While Cells(lR, 8) <> ""
    Cells(lR, 5) = Mid(Cells(lR, 7), 1, 6)
    Cells(lR, 3).Select
    lCust = Cells(lR, 3).End(xlUp).Row
    Cells(lR, 3).Select
    Cells(lR, 3) = Mid(Cells(lCust, 3), 1, 6)
    lR = lR + 1
    Wend
    lR = lR + 1
    Loop

    It may not be pretty - but it works and with your post on Personal, I can now use it monthly! Thanks again.

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mid & Variable Range (97 SR1)

    If the "values" are entered as text not numerical values, then the If statement would have to test form <> "0". If that is not the problem, I would have to see the sheet to know what the problem is.

    Two more comments. First, your macro will run a lot faster and the screen will not flash if you remove those two .Select lines which do not appear to be accomplishing anything. Second, that last IR = IR + 1 statement could cause you to miss some rows that should be processed.
    Legare Coleman

Posting Permissions

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