PDA

View Full Version : Excel VBA Row Generation if Value Exists



j.smith1981
03-23-2009, 02:23 AM
Hi there,

I have a bit of a problem I cant map out an idea in Excel, or dont know where to start with it really.

What I have is 2 workbooks basically.

One with a full list of Ink Cartridges, with the product code next to the price value.

Say there took up just 2 columns completely (forgetting about the rest of the junk as I can amend this macro later on).

There's then a 2nd workbook, with what printers their compatible with, still with me?

What I was wanting is for them to generate categories on the 1st sheet I said above automatically from the products list.

Could some help me in writing a macro for this?

I do appreciate its a big one, but your assistance would be greatly appreciated thanks.

Jeremy.

PS Could you give clear examples of how each part is meant to work aswell so's I can amend it easier please.

Bob Phillips
03-23-2009, 04:10 AM
Is this 2 workbooks, or just 2 worksheets? If the former, I would suggest you change it to the latter.

Can you post a workbook to save us the effort?

j.smith1981
03-23-2009, 08:15 AM
I'll try and put a diagram just to explain it,

The products list looks like this at the moment:

productcode product name description root category
57454541 lc02bk description for cart 1 brother

Then this is the printer models list (just the item above being in it though)
cartridgecode printer model brand
lc02bk 5740 brother


I would therefore basically like the macro to; go down the printers list, find lc02bk (there's literally over 9,000 cartridge items in our list) put brother in then delimit the brand from the printer model number with a '/'.

Would this be possible?

There's roughly 714 products, and thousands of printer models as I have explained.

j.smith1981
03-23-2009, 08:31 AM
here's the printer models list (kept it rather simple but apparent

j.smith1981
03-23-2009, 08:32 AM
and here's the product list, just as an example its only 3 fields/columns I am interested in and I think you can see which ones they are from the description on the modelExample.xls file.

j.smith1981
03-24-2009, 06:36 AM
Does anyone have an answer to my problem?

Thanks,
Jeremy.

Aussiebear
03-24-2009, 02:59 PM
Can you combine the two workbooks?

j.smith1981
03-25-2009, 06:22 AM
I actually have a version working, albeit probably not the most efficient way of doing it, good old loops haha!

Bit complex so I wont explain the whole lot but some of you may know how it works by looking at it, as I have said I think previously I'm relatively new to VBA in Excel so I apologise for my slopping programming skills.

I am learning an awfull lot though by speaking to you guys so thanks in advance once again, wouldnt have dreamed to do the code below, in previous years.

Anyways enough of the blabbing on:

with the:

Do
If ActiveCell.Formula = productcode Then
'MsgBox "code exists"
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "Code found"
ActiveCell.Offset(0, -1).Select
End If

Is there anyway possible, as at the moment the cell containing the product codes on the data.xls file must only contain product codes, it has some other data in there to do with the corresponding part no in there.

Would it be possible for anyone to help me use the Find function to make it search for say within the cell selected itself, rather than going for the entire cell value?:banghead:


Here's the full macro I have written:
Sub subCategoryMaker()
Application.DisplayAlerts = False

Dim fileLoc As String
Dim lastCSV As String

fileLoc = ThisWorkbook.Path

Cells(21, 5).Select 'selects E21 (last CSV processed)
lastCSV = ActiveCell.Value 'Saves value as variable

Application.Workbooks.Open (fileLoc & "\" & lastCSV) ' Opens the last generated CSV file

Application.Workbooks.Open (fileLoc & "\" & "dataSheet.xls") ' Opens the last generated CSV file

Application.Workbooks(lastCSV).Activate

Cells(3, 2).Select

Dim productcode As String

Do While IsEmpty(ActiveCell.Offset(0, 0)) = False ' Loops until current cell is empty?

productcode = ActiveCell.Value 'Saves the current cell value as variable

Application.Workbooks("dataSheet.xls").Activate 'Activates data sheet
Sheets("subCategories").Select

Cells(2, 4).Select

Do
If ActiveCell.Formula = productcode Then
'MsgBox "code exists"
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "Code found"
ActiveCell.Offset(0, -1).Select
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

Application.Workbooks(lastCSV).Activate
ActiveCell.Offset(1, 0).Select 'Happens after the current productcode has been found/not found
Loop
Cells(1, 1).Select

End Sub

j.smith1981
03-25-2009, 06:35 AM
Is this 2 workbooks, or just 2 worksheets? If the former, I would suggest you change it to the latter.

Can you post a workbook to save us the effort?
I have done the above macro that way because, with CSV files, which is what it will be saved as, cant have multiple sheets when saved, can only have one, if there's multiple then it just accepts the first sheet and then removes the others to the right of the first sheet.

X Cart which this is all for, importing products and generating categories for items, accepts only CSV files for its MySQL database.

Not much I can do about that really, just the way CSV's work as you probably know.