Trouble with Loop looking for blank cells

KristenT

New Member
Joined
Aug 10, 2006
Messages
16
Hey guys, having a problem here.
Below is the code that I've written, and mostly it works... just one minor (ok, major) point that isn't.
This is connected to a User form with inputs PartNo, Count, and UOM. What I want is for the user to be able to enter the three values and have it search down the list to see if there is a matching value. If there is, I want it to add the counts together. If there isn't, I want it to search for the next blank and write the values in. If the partno already exists it's working fine, it's the blank that I can't figure out. My loop isn't working properly and I keep getting interrupted by a screaming boss so I can't even tell if my logic is right. Please help! Thank you. Kristen

Code:
ActiveWorkbook.Sheets("Data").Activate
Range("A2").Select
        
    If ActiveCell = "" Then
        ActiveCell.Value = PartNo.Value
        ActiveCell.Offset(0, 3) = Price.Value
        ActiveCell.Offset(0, 2) = Count.Value
        ActiveCell.Offset(0, 1) = UOM.Value
    Else:
        Do
        If ActiveCell = PartNo.Value Then
            ActiveCell.Offset(0, 3) = Price.Value
            ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 2) + Count.Value
            ActiveCell.Offset(0, 1) = UOM.Value
        Else
            ActiveCell.Offset(1, 0).Select
     
        Loop Until IsEmpty(ActiveCell) = True
     End If
     
    End If
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Kristen

What type of controls are you using?

Have you tried using a combobox for the part nos?

It could be populated with the existing part nos on the sheet, and depending on how they are organised, you could use the ListIndex of the combobox to locate the row of the selected part no.

If the user enters a non-existent part no then the ListIndex will be -1, so you will know you need to find the next blank row.

There are various ways to find the next blank row, one of which is this.
Code:
NextRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row+1
 

KristenT

New Member
Joined
Aug 10, 2006
Messages
16
ha, I knew if anyone had any clue it would be you. ;)
Any chance I can just email you the spreadsheet? I don't think I got across very well what I needed it to do. I don't need it to just find the next blank cell, I need it to compare every entry already populated in the spreadsheet (yes, from a combobox for partno) and IF there is one the same to add it, and if no match to go to the first blank.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Kristen

Don't think it's at the stage of needing to send the workbook.:)

Can you answer a few questions and hopefully I'll be able to clarify what I mean?

How is the combobox populated with the part nos?

Are there any duplicate part nos?

Where on the Data sheet are they located?
 

KristenT

New Member
Joined
Aug 10, 2006
Messages
16

ADVERTISEMENT

Nori,

I use this code to populate the combo box from a list of part numbers from our main system.
Code:
LastRow1 = Sheets("ItemList").Range("A" & Rows.Count).End(xlUp).Row
    PartNo.RowSource = "ItemList!A2:A" & LastRow1

There are no duplicate part numbers in the list from which it is populated, but there are duplicate in the list that the UserForm populates. Well, not duplicates, because if it finds it in the spreadsheet that it is poulating it should add to the original total, not add a new line.
The spreadsheet that it is poulating is very simple, just 4 columns, populated by the feilds on the user form.
As shown below, if the next item is 55SW:08 then I want it to add on to the count of 119 (and it does, or did at least, trouble with the loop). If it is any other item from the list then I want it to write the item below, with it's own count. Does that answer your query?

Item # UOM Count Price per UOM
55SW:08 EA 119
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Kristen

Apologies for not getting back to you.

I think your last post kind of threw me.

I was half way through writing a reply when I reread it.

The part that confused me is that you mentioned that there could be duplicate part nos in the list you want to populate.

If that's the case how would you decide which of the duplicates to add the amount to?
 

KristenT

New Member
Joined
Aug 10, 2006
Messages
16
Um, I'm not sure what I meant by that.... there would not be duplicates because it would add to the already existing entry. If you check the link you might get a better idea and can download the zip file. my boss is hounding me again, she thinks this stuff is easy for me. I'm an estimator, not a programmer! And I have 145 quotes to complete....plus all our new catalogue pricing to set! *sigh*
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Kristen

So there won't be duplicates on the Data tab?
 

Forum statistics

Threads
1,136,503
Messages
5,676,230
Members
419,615
Latest member
jda2000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top