Mid & Variable Range (97 SR1)

Topic

New Reply

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.

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

Sorry — I don’t get it. Maybe a very precise example would help.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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:

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

Reply | Quote

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

Reply | Quote

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 adapted 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

Reply | Quote

Maybe this is closer to what you want:

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote