| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Code using A sheet to find a number
Can someone help please
I would like for it to look at sheet2 and see if "V014989" Is in the column A Row 2:65535 Private Sub StoreAccountNumbers() On Error Resume Next With ActiveSheet If .Range("B3").Value = "V014989" Then .Name = "101_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value = "V014990" Then .Name = "102_" & Format(Now(), "[$-409]yyyymmdd;@") End If End With With ActiveSheet If .Range("B3").Value = "V014991" Then .Name = "103_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value "V014991" Then .Name = "Unknown_" & Format(Now(), "[$-409]yyyymmdd;@") End If If Err.Number < 0 Then MsgBox Err.Number & " -- " & Err.Description End If On Error GoTo 0 End With 'PromptForSave 'SaveWorkbookToFolder End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code using A sheet to find a number
Its looking on sheet1 B3
would like to see if sheet1 B3 Value is on sheet2 A3:A65535 and if it is then use the Value in Column B next to the match "Don Guillett" wrote: What you have now is looking for one instance of the value in cell b3. This would be much easier with a select case statement. My question is: Are you looking for all three of these? And, are there more than one instance? Maybe you want. Sub findem() myarray = Array("a", "b", "c") For Each c In myarray x = Columns(4).Find(c) Select Case UCase(x) Case "A": y = 1 Case "B": y = 2 Case "C": y = 3 Case Else End Select MsgBox y Next End Sub -- Don Guillett SalesAid Software "Mike" wrote in message ... Can someone help please I would like for it to look at sheet2 and see if "V014989" Is in the column A Row 2:65535 Private Sub StoreAccountNumbers() On Error Resume Next With ActiveSheet If .Range("B3").Value = "V014989" Then .Name = "101_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value = "V014990" Then .Name = "102_" & Format(Now(), "[$-409]yyyymmdd;@") End If End With With ActiveSheet If .Range("B3").Value = "V014991" Then .Name = "103_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value "V014991" Then .Name = "Unknown_" & Format(Now(), "[$-409]yyyymmdd;@") End If If Err.Number < 0 Then MsgBox Err.Number & " -- " & Err.Description End If On Error GoTo 0 End With 'PromptForSave 'SaveWorkbookToFolder End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code using A sheet to find a number
OK. Now, Is there MORE than one instance of the value to look for. Or, Just
ONE.... -- Don Guillett SalesAid Software "Mike" wrote in message ... Its looking on sheet1 B3 would like to see if sheet1 B3 Value is on sheet2 A3:A65535 and if it is then use the Value in Column B next to the match "Don Guillett" wrote: What you have now is looking for one instance of the value in cell b3. This would be much easier with a select case statement. My question is: Are you looking for all three of these? And, are there more than one instance? Maybe you want. Sub findem() myarray = Array("a", "b", "c") For Each c In myarray x = Columns(4).Find(c) Select Case UCase(x) Case "A": y = 1 Case "B": y = 2 Case "C": y = 3 Case Else End Select MsgBox y Next End Sub -- Don Guillett SalesAid Software "Mike" wrote in message ... Can someone help please I would like for it to look at sheet2 and see if "V014989" Is in the column A Row 2:65535 Private Sub StoreAccountNumbers() On Error Resume Next With ActiveSheet If .Range("B3").Value = "V014989" Then .Name = "101_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value = "V014990" Then .Name = "102_" & Format(Now(), "[$-409]yyyymmdd;@") End If End With With ActiveSheet If .Range("B3").Value = "V014991" Then .Name = "103_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value "V014991" Then .Name = "Unknown_" & Format(Now(), "[$-409]yyyymmdd;@") End If If Err.Number < 0 Then MsgBox Err.Number & " -- " & Err.Description End If On Error GoTo 0 End With 'PromptForSave 'SaveWorkbookToFolder End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code using A sheet to find a number
No Just one instance of the Value
"Don Guillett" wrote: OK. Now, Is there MORE than one instance of the value to look for. Or, Just ONE.... -- Don Guillett SalesAid Software "Mike" wrote in message ... Its looking on sheet1 B3 would like to see if sheet1 B3 Value is on sheet2 A3:A65535 and if it is then use the Value in Column B next to the match "Don Guillett" wrote: What you have now is looking for one instance of the value in cell b3. This would be much easier with a select case statement. My question is: Are you looking for all three of these? And, are there more than one instance? Maybe you want. Sub findem() myarray = Array("a", "b", "c") For Each c In myarray x = Columns(4).Find(c) Select Case UCase(x) Case "A": y = 1 Case "B": y = 2 Case "C": y = 3 Case Else End Select MsgBox y Next End Sub -- Don Guillett SalesAid Software "Mike" wrote in message ... Can someone help please I would like for it to look at sheet2 and see if "V014989" Is in the column A Row 2:65535 Private Sub StoreAccountNumbers() On Error Resume Next With ActiveSheet If .Range("B3").Value = "V014989" Then .Name = "101_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value = "V014990" Then .Name = "102_" & Format(Now(), "[$-409]yyyymmdd;@") End If End With With ActiveSheet If .Range("B3").Value = "V014991" Then .Name = "103_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value "V014991" Then .Name = "Unknown_" & Format(Now(), "[$-409]yyyymmdd;@") End If If Err.Number < 0 Then MsgBox Err.Number & " -- " & Err.Description End If On Error GoTo 0 End With 'PromptForSave 'SaveWorkbookToFolder End Sub |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code using A sheet to find a number
If, as I understand it, you are trying to match the value in cell b3 to a
cell on sheet2 in col A and use the value in the next column, then it seems to me that a simple vlookup FORMULA would do this for you without code. Or, am I still not understanding? =vlookup(sheet1!b3,sheet2!a3:b1000,2,0) -- Don Guillett SalesAid Software "Mike" wrote in message ... No Just one instance of the Value "Don Guillett" wrote: OK. Now, Is there MORE than one instance of the value to look for. Or, Just ONE.... -- Don Guillett SalesAid Software "Mike" wrote in message ... Its looking on sheet1 B3 would like to see if sheet1 B3 Value is on sheet2 A3:A65535 and if it is then use the Value in Column B next to the match "Don Guillett" wrote: What you have now is looking for one instance of the value in cell b3. This would be much easier with a select case statement. My question is: Are you looking for all three of these? And, are there more than one instance? Maybe you want. Sub findem() myarray = Array("a", "b", "c") For Each c In myarray x = Columns(4).Find(c) Select Case UCase(x) Case "A": y = 1 Case "B": y = 2 Case "C": y = 3 Case Else End Select MsgBox y Next End Sub -- Don Guillett SalesAid Software "Mike" wrote in message ... Can someone help please I would like for it to look at sheet2 and see if "V014989" Is in the column A Row 2:65535 Private Sub StoreAccountNumbers() On Error Resume Next With ActiveSheet If .Range("B3").Value = "V014989" Then .Name = "101_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value = "V014990" Then .Name = "102_" & Format(Now(), "[$-409]yyyymmdd;@") End If End With With ActiveSheet If .Range("B3").Value = "V014991" Then .Name = "103_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value "V014991" Then .Name = "Unknown_" & Format(Now(), "[$-409]yyyymmdd;@") End If If Err.Number < 0 Then MsgBox Err.Number & " -- " & Err.Description End If On Error GoTo 0 End With 'PromptForSave 'SaveWorkbookToFolder End Sub |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Code using A sheet to find a number
That will work but I would like it to be in code instead.
"Don Guillett" wrote: If, as I understand it, you are trying to match the value in cell b3 to a cell on sheet2 in col A and use the value in the next column, then it seems to me that a simple vlookup FORMULA would do this for you without code. Or, am I still not understanding? =vlookup(sheet1!b3,sheet2!a3:b1000,2,0) -- Don Guillett SalesAid Software "Mike" wrote in message ... No Just one instance of the Value "Don Guillett" wrote: OK. Now, Is there MORE than one instance of the value to look for. Or, Just ONE.... -- Don Guillett SalesAid Software "Mike" wrote in message ... Its looking on sheet1 B3 would like to see if sheet1 B3 Value is on sheet2 A3:A65535 and if it is then use the Value in Column B next to the match "Don Guillett" wrote: What you have now is looking for one instance of the value in cell b3. This would be much easier with a select case statement. My question is: Are you looking for all three of these? And, are there more than one instance? Maybe you want. Sub findem() myarray = Array("a", "b", "c") For Each c In myarray x = Columns(4).Find(c) Select Case UCase(x) Case "A": y = 1 Case "B": y = 2 Case "C": y = 3 Case Else End Select MsgBox y Next End Sub -- Don Guillett SalesAid Software "Mike" wrote in message ... Can someone help please I would like for it to look at sheet2 and see if "V014989" Is in the column A Row 2:65535 Private Sub StoreAccountNumbers() On Error Resume Next With ActiveSheet If .Range("B3").Value = "V014989" Then .Name = "101_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value = "V014990" Then .Name = "102_" & Format(Now(), "[$-409]yyyymmdd;@") End If End With With ActiveSheet If .Range("B3").Value = "V014991" Then .Name = "103_" & Format(Now(), "[$-409]yyyymmdd;@") ElseIf .Range("B3").Value "V014991" Then .Name = "Unknown_" & Format(Now(), "[$-409]yyyymmdd;@") End If If Err.Number < 0 Then MsgBox Err.Number & " -- " & Err.Description End If On Error GoTo 0 End With 'PromptForSave 'SaveWorkbookToFolder End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VB code to copy sheet format to another sheet | Excel Discussion (Misc queries) | |||
| Why code for find() is not working | Excel Discussion (Misc queries) | |||
| to find number of days between 2 dates using vba code in excel | Excel Discussion (Misc queries) | |||
| how do i create an access code sheet to number my office files an. | New Users to Excel | |||
| Find all text instances in a sheet and add one number from each row | Excel Discussion (Misc queries) | |||
Linear Mode
