Posts by zuko

I have an excel file with about 50-60 workbooks inside of it each containing data.

I work for Albertsons and each tab is an individual store

For example:

One store (tab) might say

Crackers 4ft

Another store (tab) might say

Crackers 12ft

I want to go across workbooks and have it count how many stores use the 4ft set and how many use the 12ft set. I'm not sure if this is possible or how to even go about doing it. Any help would be much appreciated. Thank you.

Re: Searching for specific text

Had to modify it a little bit but "like" is what I was looking for. Thanks a lot for your help!

Dim row As Integer
    row = 8
For row = 8 To Rownum
    FourNum = Cells(row, 13).Value
    Rmap = Cells(row, 10).Value
    CRMAP = Cells(row, 5).Value
    CRMAD = Cells(row, 4).Value
    Descript = Cells(row, 3).Value
'Highlight Deletes
    If FourNum < AFourwk And CRMAD > ACRMAD And CRMAP > ACRMAPercent Then Rows(row).Interior.ColorIndex = 37
    If FourNum > SFourwk And Rmap < SRMAPercent And CRMAP < SCRMAPercent Then Rows(row).Interior.ColorIndex = 35
    If Cells(row, 3).Value = "=search(""private"",descript)" = 1 Then Rows(row).Interior.ColorIndex = xlNone
Next row

This is what I have for my code

If Cells(row, 3).Value = "=search(""private"",descript)" = 1 Then Rows(row).Interior.ColorIndex = xlNone

Looking at this part specifically. What I want the code to do is look in column 3 of the worksheet for every instance of word "private" and when it finds it, I want it to shade that whole row clear.

Any ideas? Thanks a lot for the help.

Below is the code I currently have which will open workbooks Run my macro then save them into the same folder and go onto the next workbook.

Sub DBSSReport()
sFileName = Dir("P:\SMARTeam\SMARTeam Files\Rankings\DBSS Files\Macro Ready\" & "*.xls")
Do While sFileName <> ""
    Set Wkb = Workbooks.Open("P:\SMARTeam\SMARTeam Files\Rankings\DBSS Files\Macro Ready\" & sFileName)
    Call MainBeast
Wkb.Close (True)
sFileName = Dir
Loop
End Sub

What I need is to open a workbook from the above folder then save to a different folder with the path of P:\SMARTeam\SMARTeam Files\Rankings\DBSS Files\Final
Any Suggestions?

Re: Vlookup using VBA

Yes could you please post the new code in order to get the macro to fill in the Vlookup?? Thanks

Re: Vlookup using VBA

I must have missed your last post for some reason. I got it to work, thank you very much. One additional question though.

In your code

For Each ws In Worksheets 
    If ws.Name = "Sheet1" Then Goto JeffRocks3 
    ws.Select 
    C = 14 
    For C = 14 To 13 + SNum 
        Cells(7, C).Formula = "=VLOOKUP(C7,Sheet1!$A$2:$BB$3000," & C - 10 & ",False)" 
    Next C 
    Range("A1").Select 
JeffRocks3: 
     'Sheet 1 is Skipped
Next ws 
Sheets("1").Select 
 
End Sub

Everything works but when I do an autofill to go down it takes forever to calculate all those cells. Is there a faster way around it by chance? Way to just paste the values? Thanks!

I posted this topic before but no responses seemed to help. I will try a different approach to it.

Below are the values that need to be imported into the next sheet (picture2) referenced through UPC.

[Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/gib1.jpg]

[Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/gib2.jpg]

I need to be able to do this for multiple workbooks therefore needs to be written in VBA and ran by the macro. Is it possible to put the data into an array and use vlookup to return only the result of the vlookup into the sheet (picture 2).

It is really difficult to explain this in text, so if anyone needs more clarification, please let me know. Thank you all in advance.

Re: VLOOKUP Issues

[Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/blah3.jpg]

[Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/blah4.jpg]

Notice how the column and cell values change.

I can do all this manually, it takes time. I wish to automate it, create some type of a loop.

Any ideas would be much appreciated!

Re: VLOOKUP Issues

I don't think I explained it well enough for you guys. I took some more screen shots of what I want to do.

[Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/blah1.JPG]

I start off with something like this

[Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/blah2.jpg]

I use the vlookup to match what's in column C to what it is in the DBSS worksheet

I need help with making vlookup go all the way across my columns and down. Is it possible to have lookup_value and column_index both be variable values? If it isn't possible, is there another way this can be done?

For Each ws In Worksheets
    If ws.Name = "Sheet1" Then GoTo JeffRocks3
    ws.Select
    C = 14
    For C = 14 To 13 + SNum
        Cells(7, C).Formula = "=VLOOKUP(C7,Sheet1!$A$2:$BB$3000,Match(N5,Sheet1!$D$1:$BB$1,FALSE)+3,False)"
    Next C
    Range("A1").Select
JeffRocks3:
    'Sheet 1 is Skipped
Next ws
Sheets("1").Select

As of right now, I got the vlook up to go across, but the the cell value stays the same all the way across, I would need it to change going from one column to another, if this makes sense.

[Blocked Image: http://www.zuko.org/uploader/./userfiles/zuko/gibson.jpg]