Excel 2007 : Subtraction

[GaidenFocus]

Hey guys quick question,

I have a user form set up, that when activated asks for a part number and QTY. i need the QTY field to subtract the number i type in there from a specific cell. the cell will be on different sheets based on the part number.

i know how to reference sheets and cells i just need to know how to write a subtraction function.

thanks in advance!

[JBeaucaire]

Once you spot the cell (100s of ways), then it would be something along the lines of:

FoundCell.Value = FoundCell.Value - QTY.Value

[GaidenFocus]

ok how would i apply that to an offset? i have a loop set up to search through the list, find the part number, the subtract qty.text from the cell next to the matching cell.

 Do Until IsEmpty(ActiveCell)

IF activecell.value = location.text then
  if activecell.offset(0, 1) = tagid.text then
activecell.offset(0, 1).value = activecell.offset(0, 1).value - qty.txt

 ActiveCell.Offset(1, 0).Select
 Loop

does this look right? i tried something similar yesterday and it crashed excel. is there a better way to match a text box to a list besides the loop?

[JBeaucaire]

You're looping cell by cell to find the part number instead of just jumping right to it? Also your use of Activecell makes it look like you're selecting cells over and over again like a human might do. VBA has no need to select.

Dim PartFOUND as Range

On Error Resume Next
Set PartFOUND = Sheets("Inventory").Range("A:A").Find(TagID.Text, LookIn:=xlValues, LookAt:=xlWhole)

If Not PartFOUND is Nothing Then 
    PartFOUND.Offset(,1).Value = PartFOUND.Offset(,1).Value - Qty.Text
    Set PartFOUND = Nothing
End If

[GaidenFocus]

man thats great. i knew there was a better way i was just working with what i knew. thanks for all the help man.

i've been building a makeshift inventory control system for a few months now, at my new job they just went and counted things every time they needed to know how much of a part number they had. there are over 300 part numbers so the task was tedious. with your help and a few others on here its been coming together nicely. thanks for all the speedy replies!

oh and just so you know, before i post on here i google what im trying to do extensively and try various things i find before i post on here. i even use the record macro tool which has helped alot. just dont want you guys getting mad at me for making so many posts.

anyways thanks!

[JBeaucaire]

We won't get angry unless you don't listen to what you learn.

The macro recorder is a great tool for getting "base code", but it's recording human actions, so it's humanistic code and all the selecting needs to be restructured out. But I still use the recorder every day to figure out parameters and syntax.

Cheers.