| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting msg box for no match!
hi all!
if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting msg box for no match!
Dim res as Variant
res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) if iserror(res) then msgbox "No Match" else ws.Cells(iRow, 1).Value = res end if -- Regards, Tom Ogilvy "via135" wrote in message oups.com... hi all! if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting msg box for no match!
On Feb 24, 10:36 am, "Tom Ogilvy" wrote:
Dim res as Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) if iserror(res) then msgbox "No Match" else ws.Cells(iRow, 1).Value = res end if -- Regards, Tom Ogilvy "via135" wrote in message oups.com... hi all! if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135- Hide quoted text - - Show quoted text - still i am not getting the error msg box if there is no match! this is my code.. Private Sub CmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a micr number If Trim(Me.txtMicr.Value) = "" Then Me.txtMicr.SetFocus MsgBox "Please enter a Micr number" Exit Sub End If 'add records to database Dim res As Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) If IsError(res) Then MsgBox "No Match" Else ws.Cells(iRow, 1).Value = res End If ws.Cells(iRow, 2).Value = Me.txtChqno.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value 'clear the data Me.txtMicr.Value = "" Me.txtChqno.Value = "" Me.txtAmount.Value = "" Me.txtMicr.SetFocus End Sub help pl? regds! -via135 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting msg box for no match!
It worked fine for me.
-- Regards, Tom Ogilvy "via135" wrote in message oups.com... On Feb 24, 10:36 am, "Tom Ogilvy" wrote: Dim res as Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) if iserror(res) then msgbox "No Match" else ws.Cells(iRow, 1).Value = res end if -- Regards, Tom Ogilvy "via135" wrote in message oups.com... hi all! if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135- Hide quoted text - - Show quoted text - still i am not getting the error msg box if there is no match! this is my code.. Private Sub CmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a micr number If Trim(Me.txtMicr.Value) = "" Then Me.txtMicr.SetFocus MsgBox "Please enter a Micr number" Exit Sub End If 'add records to database Dim res As Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) If IsError(res) Then MsgBox "No Match" Else ws.Cells(iRow, 1).Value = res End If ws.Cells(iRow, 2).Value = Me.txtChqno.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value 'clear the data Me.txtMicr.Value = "" Me.txtChqno.Value = "" Me.txtAmount.Value = "" Me.txtMicr.SetFocus End Sub help pl? regds! -via135 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting msg box for no match!
On Feb 25, 11:10 pm, "Tom Ogilvy" wrote:
It worked fine for me. -- Regards, Tom Ogilvy "via135" wrote in message oups.com... On Feb 24, 10:36 am, "Tom Ogilvy" wrote: Dim res as Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) if iserror(res) then msgbox "No Match" else ws.Cells(iRow, 1).Value = res end if -- Regards, Tom Ogilvy "via135" wrote in message groups.com... hi all! if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135- Hide quoted text - - Show quoted text - still i am not getting the error msg box if there is no match! this is my code.. Private Sub CmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a micr number If Trim(Me.txtMicr.Value) = "" Then Me.txtMicr.SetFocus MsgBox "Please enter a Micr number" Exit Sub End If 'add records to database Dim res As Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) If IsError(res) Then MsgBox "No Match" Else ws.Cells(iRow, 1).Value = res End If ws.Cells(iRow, 2).Value = Me.txtChqno.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value 'clear the data Me.txtMicr.Value = "" Me.txtChqno.Value = "" Me.txtAmount.Value = "" Me.txtMicr.SetFocus End Sub help pl? regds! -via135- Hide quoted text - - Show quoted text - yes..it works when i open a new workbook and create a new user form! but believe me it doesn't work when i edit the vb code in my earlier workbook..! i don't know the reason..! anyhow thks for the help! btw one more help..! if there is same input value for successive records for any given txtbox, is there any way to assign a macro to function key for bring the previous value? hope u understand..! -via135 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting msg box for no match!
for finding multiple instances of a value, try using the help code for the
FindNext command rather than worksheet functions. -- Regards, Tom Ogilvy "via135" wrote in message oups.com... On Feb 25, 11:10 pm, "Tom Ogilvy" wrote: It worked fine for me. -- Regards, Tom Ogilvy "via135" wrote in message oups.com... On Feb 24, 10:36 am, "Tom Ogilvy" wrote: Dim res as Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) if iserror(res) then msgbox "No Match" else ws.Cells(iRow, 1).Value = res end if -- Regards, Tom Ogilvy "via135" wrote in message groups.com... hi all! if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135- Hide quoted text - - Show quoted text - still i am not getting the error msg box if there is no match! this is my code.. Private Sub CmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a micr number If Trim(Me.txtMicr.Value) = "" Then Me.txtMicr.SetFocus MsgBox "Please enter a Micr number" Exit Sub End If 'add records to database Dim res As Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) If IsError(res) Then MsgBox "No Match" Else ws.Cells(iRow, 1).Value = res End If ws.Cells(iRow, 2).Value = Me.txtChqno.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value 'clear the data Me.txtMicr.Value = "" Me.txtChqno.Value = "" Me.txtAmount.Value = "" Me.txtMicr.SetFocus End Sub help pl? regds! -via135- Hide quoted text - - Show quoted text - yes..it works when i open a new workbook and create a new user form! but believe me it doesn't work when i edit the vb code in my earlier workbook..! i don't know the reason..! anyhow thks for the help! btw one more help..! if there is same input value for successive records for any given txtbox, is there any way to assign a macro to function key for bring the previous value? hope u understand..! -via135 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting msg box for no match!
On Feb 27, 8:13 am, "Tom Ogilvy" wrote:
for finding multiple instances of a value, try using the help code for the FindNext command rather than worksheet functions. -- Regards, Tom Ogilvy "via135" wrote in message oups.com... On Feb 25, 11:10 pm, "Tom Ogilvy" wrote: It worked fine for me. -- Regards, Tom Ogilvy "via135" wrote in message groups.com... On Feb 24, 10:36 am, "Tom Ogilvy" wrote: Dim res as Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) if iserror(res) then msgbox "No Match" else ws.Cells(iRow, 1).Value = res end if -- Regards, Tom Ogilvy "via135" wrote in message groups.com... hi all! if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135- Hide quoted text - - Show quoted text - still i am not getting the error msg box if there is no match! this is my code.. Private Sub CmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a micr number If Trim(Me.txtMicr.Value) = "" Then Me.txtMicr.SetFocus MsgBox "Please enter a Micr number" Exit Sub End If 'add records to database Dim res As Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) If IsError(res) Then MsgBox "No Match" Else ws.Cells(iRow, 1).Value = res End If ws.Cells(iRow, 2).Value = Me.txtChqno.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value 'clear the data Me.txtMicr.Value = "" Me.txtChqno.Value = "" Me.txtAmount.Value = "" Me.txtMicr.SetFocus End Sub help pl? regds! -via135- Hide quoted text - - Show quoted text - yes..it works when i open a new workbook and create a new user form! but believe me it doesn't work when i edit the vb code in my earlier workbook..! i don't know the reason..! anyhow thks for the help! btw one more help..! if there is same input value for successive records for any given txtbox, is there any way to assign a macro to function key for bring the previous value? hope u understand..! -via135- Hide quoted text - - Show quoted text - sorry..i'm a novice to VBA that's why asking for help in this group! regds! -via135 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting msg box for no match!
that means go into the Visual Basic editor and going into Help. Then using
the help selection in the menu to find the command findnext. In the help text will be sample code showing how to use code to fine multiple instances of a value in a range. So, it is giving you help. -- Regards, Tom Ogilvy "via135" wrote in message ps.com... On Feb 27, 8:13 am, "Tom Ogilvy" wrote: for finding multiple instances of a value, try using the help code for the FindNext command rather than worksheet functions. -- Regards, Tom Ogilvy "via135" wrote in message oups.com... On Feb 25, 11:10 pm, "Tom Ogilvy" wrote: It worked fine for me. -- Regards, Tom Ogilvy "via135" wrote in message groups.com... On Feb 24, 10:36 am, "Tom Ogilvy" wrote: Dim res as Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) if iserror(res) then msgbox "No Match" else ws.Cells(iRow, 1).Value = res end if -- Regards, Tom Ogilvy "via135" wrote in message groups.com... hi all! if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135- Hide quoted text - - Show quoted text - still i am not getting the error msg box if there is no match! this is my code.. Private Sub CmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a micr number If Trim(Me.txtMicr.Value) = "" Then Me.txtMicr.SetFocus MsgBox "Please enter a Micr number" Exit Sub End If 'add records to database Dim res As Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) If IsError(res) Then MsgBox "No Match" Else ws.Cells(iRow, 1).Value = res End If ws.Cells(iRow, 2).Value = Me.txtChqno.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value 'clear the data Me.txtMicr.Value = "" Me.txtChqno.Value = "" Me.txtAmount.Value = "" Me.txtMicr.SetFocus End Sub help pl? regds! -via135- Hide quoted text - - Show quoted text - yes..it works when i open a new workbook and create a new user form! but believe me it doesn't work when i edit the vb code in my earlier workbook..! i don't know the reason..! anyhow thks for the help! btw one more help..! if there is same input value for successive records for any given txtbox, is there any way to assign a macro to function key for bring the previous value? hope u understand..! -via135- Hide quoted text - - Show quoted text - sorry..i'm a novice to VBA that's why asking for help in this group! regds! -via135 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting msg box for no match!
On Feb 28, 7:30 am, "Tom Ogilvy" wrote:
that means go into the Visual Basic editor and going into Help. Then using the help selection in the menu to find the command findnext. In the help text will be sample code showing how to use code to fine multiple instances of a value in a range. So, it is giving you help. -- Regards, Tom Ogilvy "via135" wrote in message ps.com... On Feb 27, 8:13 am, "Tom Ogilvy" wrote: for finding multiple instances of a value, try using the help code for the FindNext command rather than worksheet functions. -- Regards, Tom Ogilvy "via135" wrote in message groups.com... On Feb 25, 11:10 pm, "Tom Ogilvy" wrote: It worked fine for me. -- Regards, Tom Ogilvy "via135" wrote in message groups.com... On Feb 24, 10:36 am, "Tom Ogilvy" wrote: Dim res as Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) if iserror(res) then msgbox "No Match" else ws.Cells(iRow, 1).Value = res end if -- Regards, Tom Ogilvy "via135" wrote in message groups.com... hi all! if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135- Hide quoted text - - Show quoted text - still i am not getting the error msg box if there is no match! this is my code.. Private Sub CmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a micr number If Trim(Me.txtMicr.Value) = "" Then Me.txtMicr.SetFocus MsgBox "Please enter a Micr number" Exit Sub End If 'add records to database Dim res As Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) If IsError(res) Then MsgBox "No Match" Else ws.Cells(iRow, 1).Value = res End If ws.Cells(iRow, 2).Value = Me.txtChqno.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value 'clear the data Me.txtMicr.Value = "" Me.txtChqno.Value = "" Me.txtAmount.Value = "" Me.txtMicr.SetFocus End Sub help pl? regds! -via135- Hide quoted text - - Show quoted text - yes..it works when i open a new workbook and create a new user form! but believe me it doesn't work when i edit the vb code in my earlier workbook..! i don't know the reason..! anyhow thks for the help! btw one more help..! if there is same input value for successive records for any given txtbox, is there any way to assign a macro to function key for bring the previous value? hope u understand..! -via135- Hide quoted text - - Show quoted text - sorry..i'm a novice to VBA that's why asking for help in this group! regds! -via135- Hide quoted text - - Show quoted text - hi Tom! i thk u misunderstood me! my post is regarding inputting of values in a userfrom..! and i need a shortcut to input the successive as well as repetitve input values..!..like ^D is xl's WS function..???!!! hope u understand..!!?? -via135 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting msg box for no match!
On Feb 28, 9:25 pm, "via135" wrote:
On Feb 28, 7:30 am, "Tom Ogilvy" wrote: that means go into the Visual Basic editor and going into Help. Then using the help selection in the menu to find the command findnext. In the help text will be sample code showing how to use code to fine multiple instances of a value in a range. So, it is giving you help. -- Regards, Tom Ogilvy "via135" wrote in message ups.com... On Feb 27, 8:13 am, "Tom Ogilvy" wrote: for finding multiple instances of a value, try using the help code for the FindNext command rather than worksheet functions. -- Regards, Tom Ogilvy "via135" wrote in message groups.com... On Feb 25, 11:10 pm, "Tom Ogilvy" wrote: It worked fine for me. -- Regards, Tom Ogilvy "via135" wrote in message groups.com... On Feb 24, 10:36 am, "Tom Ogilvy" wrote: Dim res as Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) if iserror(res) then msgbox "No Match" else ws.Cells(iRow, 1).Value = res end if -- Regards, Tom Ogilvy "via135" wrote in message groups.com... hi all! if there is no match available for the following code i want a msg box "No Match" and alternately i should be able to input any text value ..! is it possible?! ws.Cells(iRow, 1).Value = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), Worksheets("db").Range("A:A"), 0)) help pl? regds! -via135- Hide quoted text - - Show quoted text - still i am not getting the error msg box if there is no match! this is my code.. Private Sub CmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("data") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a micr number If Trim(Me.txtMicr.Value) = "" Then Me.txtMicr.SetFocus MsgBox "Please enter a Micr number" Exit Sub End If 'add records to database Dim res As Variant res = Application.Index(Worksheets("db").Range _ ("B:B"), Application.Match(CLng(txtMicr.Text), _ Worksheets("db").Range("A:A"), 0)) If IsError(res) Then MsgBox "No Match" Else ws.Cells(iRow, 1).Value = res End If ws.Cells(iRow, 2).Value = Me.txtChqno.Value ws.Cells(iRow, 3).Value = Me.txtAmount.Value 'clear the data Me.txtMicr.Value = "" Me.txtChqno.Value = "" Me.txtAmount.Value = "" Me.txtMicr.SetFocus End Sub help pl? regds! -via135- Hide quoted text - - Show quoted text - yes..it works when i open a new workbook and create a new user form! but believe me it doesn't work when i edit the vb code in my earlier workbook..! i don't know the reason..! anyhow thks for the help! btw one more help..! if there is same input value for successive records for any given txtbox, is there any way to assign a macro to function key for bring the previous value? hope u understand..! -via135- Hide quoted text - - Show quoted text - sorry..i'm a novice to VBA that's why asking for help in this group! regds! -via135- Hide quoted text - - Show quoted text - hi Tom! i thk u misunderstood me! my post is regarding inputting of values in a userfrom..! and i need a shortcut to input the successive as well as repetitve input values..!..like ^D is xl's WS function..???!!! hope u understand..!!?? -via135- Hide quoted text - - Show quoted text - Dave! help me pl? -via135 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match 2 Columns, Return 3rd, Differing Match Types | Excel Worksheet Functions | |||
| Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
| index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
| MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
| Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Linear Mode
