Replies

After banging my head against a brick wall trying to figure out why this thing wasn’t working, one simple change in syntax (and approach did the trick):

For Each PRow In Columns(“H:H”).SpecialCells(xlCellTypeVisible)
If PRow “” Then ….
… End If
Next PRow
and that ladies and gentlemen solved the dilemma. I am STILL slapping myself for not clueing into that simple arrangment sooner.

I thank you for your assistance in pointing me the right way.

Ooops. Spoke to soon. It seemed to work wonderfully for one stage. In another stage, using similar coding, I asked it to write a formula to one cell, then to another cell.

The problem is, is that Excel is writing the formulas down the entire column! I thought for each row select:
For Each rs In Columns(“G:G”).SpecialCells(xlCellTypeVisible)
that only the rows affected by the filter would be calculated upon. This is not the case. Once the formula is copied down to the bottom of the filtered data shown, it keeps on going.

How do I say visible cells only with value?
I used: if range(rc,”G”)”” then …
but that, and any variation to identify just cells with a value come up in error.
Am I just missing a simple syntax or something altogether?!

Vunderbar!
Simple, yet effect.
That did the trick.

Thanks.

An Array formula!
I haven’t used array formulas too much but I think that will fit the bill perfectly (after some tinkering of the tables).
Thanks!

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.

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

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.

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.

Ahh! The ol’ cover it with a blank box! Silly me for not thinking of it sooner!

Thanks for the quick help!!

If you know exactly where you are going – the button of choice for GoTo is…
F5
Nice and simple.

I had the same problem. Excel 97 SR-1, NT. I believe it was caused when someone else opened up the file. I tried everything from changing the name and location and deleting temp files. There was no way I was going to close it and lose all my changes. I did notice that the File/Properties were corrupted. I did come up with a fix. I copied each tab into a new workbook and saved the workbook under a different name! Keep it simple I say!

Yup – I’m betting OLE too! I had one colleague paste a picture in word. The file itself was about 400k. The document ballooned to 4meg and brought is some bad code (Bad code! Bad). Deleting the picture and paste special did the trick.