PDA

View Full Version : trying to lookup a number in a column and edit data in another



dccase
03-09-2007, 03:59 PM
What I'm hoping to do: In case you can't tell, I'm new to this.

Im entering an ID (via barcode scanner) and a weight, or count, etc (via print function on a scale, or hand keyed) into an input box.

I want the macro to find the material, and place the data in the same row in the proper column.

I'll only be entering data into one column at a time, and I would like to select the column when the macro starts, I can stop and restart the macro if I want to change columns.

It would be nice if the columns would be moveable and not fixed, but I don't know if that is possible, saying that I have the master list in the g column and entering data in a-f

The find formula I have now kicks me out if I enter a material that isn't on the list. I would like to be able to inventory them in another coumn.

I also would like to be prompted on an overwrite. I did have one but it isn't on here.

Any help would be appreciated

this macro doen't currently work

Sub addweights()
'
' addweights Macro
' Macro recorded 3/8/2007 by R
'
On Error GoTo HandleError
Dim colnmb
Dim c

colnumb = InputBox("please enter column letter you wish to enter data into")
Range("h18").Value = colnumb
c = colnmb - 7
Range("h19").Value = c 'test

Do
'[statements]
Dim material
material = 0
Dim weight
weight = 0
Dim ovwrite

'inputs
material = InputBox("Enter Barcode ID", "Enter Barcode")
If material = "exit" Then
GoTo exitmacro1
End If
weight = InputBox("Please press print on scale to enter weight", "Enter Weight")

Range("G:G").Find(What:=[material], After:=[G1]).Activate
ActiveCell.Offset(0, "c").Activate

ActiveCell.Value = weight

[Exit do]

Loop Until material = "exit"

'created by R on 3/8/2007
exitmacro1:
MsgBox ("ending macro")
HandleError:
End Sub

mdmackillop
03-09-2007, 04:54 PM
Hi dccase,
Welcome to VBAX
Give the following a try. I'm not clear on the overwrite, can you clarify?
Sub addweights()
'
' addweights Macro
' Macro recorded 3/8/2007 by R
'
Dim Material As String
Dim Weight As Single
Dim Col As String, colnmb As Long

On Error GoTo HandleError

Col = InputBox("please enter column letter you wish to enter data into")
colnmb = Range(Col & 1).Column

Do
'[statements]
Material = 0
Weight = 0
'inputs
Material = InputBox("Enter Barcode ID", "Enter Barcode")
If UCase(Material) = "EXIT" Then
GoTo exitmacro1
End If
Weight = InputBox("Please press print on scale to enter weight", "Enter Weight")

Range("G:G").Find(What:=Material, Lookat:=xlWhole).Offset(0, colnmb - 7).Value = Weight
Loop

exitmacro1:
MsgBox "ending macro"
HandleError:
End Sub

dccase
03-09-2007, 05:10 PM
I had it check the cell and if it was gerater than .001 I did a yes/no box if I wanted to change it.

something like this, but this was before I added other columns and the data was always next door

'ovwrite = 1



If ActiveCell.Offset(o, -1) > 0.001 Then

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to overwrite ?" ' Define message.
Style = vbYesNo + vbExclamation + vbDefaultButton2 ' Define buttons.
Title = "overwrite?" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
' ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = weight
Else ' User chose No.
' Perform some action
GoTo line1
End If
End If

If ActiveCell.Offset(o, -1) < 0.001 Then
ActiveCell.Value = weight

End If
line1:

mdmackillop
03-09-2007, 05:19 PM
Option Explicit
Sub addweights()
'
' addweights Macro
' Macro recorded 3/8/2007 by R
'
Dim Material As String
Dim msg As String
Dim Weight As Single
Dim Col As String, colnmb As Long
Dim c As Range

On Error GoTo HandleError

Col = InputBox("please enter column letter you wish to enter data into")
colnmb = Range(Col & 1).Column

Do
'[statements]
Material = 0
Weight = 0
'inputs
Material = InputBox("Enter Barcode ID", "Enter Barcode")
If UCase(Material) = "EXIT" Then GoTo exitmacro1
'Overwrite
Set c = Range("G:G").Find(What:=Material, Lookat:=xlWhole).Offset(0, colnmb - 7)
If c.Value > 0.001 Then
msg = "Existing value = " & c.Value & vbCr & "Do you wish to overwrite?"
If MsgBox(msg, vbYesNo + vbQuestion) = vbNo Then GoTo exitmacro1
End If

Weight = InputBox("Please press print on scale to enter weight", "Enter Weight")

c.Value = Weight
Loop

exitmacro1:
Set c = Nothing
MsgBox "ending macro"
HandleError:
End Sub

dccase
03-09-2007, 05:28 PM
works great but how would I make it not kick me out if I decline to replace,


msg = "Existing value = " & c.Value & vbCr & "Do you wish to overwrite?"
If MsgBox(msg, vbYesNo + vbQuestion) = vbNo Then Goto line1
End If

Weight = InputBox("Please press print on scale to enter weight", "Enter Weight")

c.Value = Weight
line1:
Loop



I answered my own question

dccase
03-09-2007, 05:53 PM
Thanks for the help. I only have one more question. How would I put the materials not found when scanned into another column, Say H. Right now it kicks me out.

I'm understanding visual basic better after studying what you wrote and what I was trying to do. The only other programming I did before was in basicA back in high school. I enjoyed the challenge and could do it, but it doesn't convert (along with my memory) to visual basic very well.

mdmackillop
03-09-2007, 06:06 PM
Same principle, but tidier
Do
'[statements]
Material = 0
Weight = 0
'inputs
Material = InputBox("Enter Barcode ID" & vbCr & "'Exit' to exit", "Enter Barcode")
If UCase(Material) = "EXIT" Then GoTo exitmacro1
'Overwrite
Set c = Range("G:G").Find(What:=Material, Lookat:=xlWhole).Offset(0, colnmb - 7)
msg = "Existing value = " & c.Value & vbCr & "Do you wish to overwrite?"
If c.Value > 0.001 And MsgBox(msg, vbYesNo + vbQuestion) = vbNo Then GoTo 1
Weight = InputBox("Please press print on scale to enter weight", "Enter Weight")
c.Value = Weight
1: Loop

dccase
03-09-2007, 06:27 PM
Do
'[statements]
Material = 0
Weight = 0
'inputs
Material = InputBox("Enter Barcode ID" & vbCr & "'Exit' to exit", "Enter Barcode")
If UCase(Material) = "EXIT" Then GoTo exitmacro1
'Overwrite
Set c = Range("G:G").Find(What:=Material, Lookat:=xlWhole).Offset(0, colnmb - 7)
msg = "Existing value = " & c.Value & vbCr & "Do you wish to overwrite?"
If c.Value > 0.001 And MsgBox(msg, vbYesNo + vbQuestion) = vbNo Then GoTo 1
Weight = InputBox("Please press print on scale to enter weight", "Enter Weight")
c.Value = Weight
1: Loop

That sends a "do you wish to overwrite" messege every time. I'll change it back to what it was, with a couple changes

Do
'[statements]
Material = 0
Weight = 0
'inputs
Material = InputBox("Enter Barcode ID", "Enter Barcode")
If UCase(Material) = "EXIT" Then GoTo exitmacro1
'Overwrite
Set c = Range("G:G").Find(What:=Material, Lookat:=xlWhole).Offset(0, colnmb - 7)
If c.Value > 0.001 Then
msg = "Existing value = " & c.Value & vbCr & "Do you wish to overwrite?"
If MsgBox(msg, vbYesNo + vbQuestion) = vbNo Then GoTo 1
End If

Weight = InputBox("Please press print on scale to enter weight", "Enter Weight")

c.Value = Weight
1: Loop

mdmackillop
03-09-2007, 06:58 PM
You're right: didn't test enough!
BTW, When you post code, select it and click the VBA button to format it as shown. It makes it more readable.

dccase
03-09-2007, 08:35 PM
Option Explicit
Sub addweights()
'
' addweights Macro
' Macro recorded 3/8/2007 by R
'
Dim Material As String
Dim msg As String
Dim Weight As Single
Dim Col As String, colnmb As Long
Dim c As Range
'Dim b As Range
Dim a
a = 2



On Error GoTo HandleError

Col = InputBox("please enter column letter you wish to enter data into")
colnmb = Range(Col & 1).Column

Do
'[statements]
Material = 0
Weight = 0
'inputs
Material = InputBox("Enter Barcode ID", "Enter Barcode")
If UCase(Material) = "EXIT" Then GoTo exitmacro1



'***help here please***

' check to see if material exists, if it doesn't, add it to column H
If Match(Material, Range("G:G"), 0) <> 0 Then
Else
Range("H", a).Value = Material
a = a + 1
GoTo 1
End If




'Overwrite
Set c = Range("G:G").Find(What:=Material, Lookat:=xlWhole).Offset(0, colnmb - 7)
If c.Value > 0.001 Then
msg = "Existing value = " & c.Value & vbCr & "Do you wish to overwrite?"
If MsgBox(msg, vbYesNo + vbQuestion) = vbNo Then GoTo 1
End If

Weight = InputBox("Please press print on scale to enter weight", "Enter Weight")

c.Value = Weight
1: Loop

exitmacro1:
Set c = Nothing
MsgBox "ending macro"
HandleError:
End Sub



I'm trying to see If an material is in column G, if it is go on to the next step, if it isn't, put it into column H then go back to scan the next material. I don't know if I'm on the right track or not?

mdmackillop
03-09-2007, 10:45 PM
From the look of things, you're going to enter repeated data items, and IMO, Inputboxes are a messy way to do this. Here's a Userform solution, basically using the same methodology you worked out.

dccase
03-09-2007, 11:44 PM
All I can say is wow, this is great. How can I fix the exit button to work on materials and not just on weight?

mdmackillop
03-10-2007, 06:16 AM
How can I fix the exit button to work on materials and not just on weight?
Can you explain what you mean?

dccase
03-10-2007, 07:13 AM
when entering data in the box, when I click on exit the macro will exit in the column and weight box, but not while in the material box.

Also, just curious, is it possible to make the material column (currently set at G) user defined. Say, I could enter it in the box to the left of the "enter column"

Will variables work here Range("G:G"), 0)

and here If txtMaterial <> "" Then
Cells(Rows.Count, 8).End(xlUp).Offset(1) = txtMaterial

mdmackillop
03-10-2007, 08:09 AM
Most things are possible. You could ulso seach row 1 for the heading "Materials" and use the result to set the position.
You need to consider how the form will be used; only by yourself or by others. Keep things as simple as possible. What does your workshheet look like.

mdmackillop
03-10-2007, 08:51 AM
This is about the best I can do. The tab order is not perfct, but returning focus to a control after clearing it makes things complicated.

dccase
03-10-2007, 09:06 AM
I'm not sure on the final number of columns that I will be entering data into. I figured 6 was safe and I could go through the macro to change it if there was more or less. Yes If it went by headers, that would be best, then it wouldn't get messed up if somebody moved a column (like the barcode column). This will be used by others in the future. There will probably be more data and headers to the right of H that will just be there to look at.

dccase
03-10-2007, 09:14 AM
thanks for trying, but I like the former one better.(like the one in my previous post. teststorage.xls) The updated one doesn't enter data in as friendly.

mdmackillop
03-10-2007, 09:17 AM
Agreed. If I come across a solution, I'll let you know, but don't hold your breath waiting for it.