Find data for a Macro with a Dynamic Range
I have data with numerous records. Each row is a record. Users add rows
about 2 times a week. The columns are organized so that particular data goes under them, like job title, or email. What I have completed is a Macro allows the users to input the data into the database. Im using input boxes and it works fine. I used Input box, code is: Cells(LINENUM, 24) = InputBox(Prompt:="What is the e-mail for the Certified Accountant ?" & vbNewLine & "Please type it below! ", Title:="E-mail for the Certified Accountant of " & FSName, Default:=CATitle) It worked great! However, it seems that the data will have an occasional column added. I cant predict where the column will be added or when that will occur. There are a great number of columns and the users want to keep the columns €ślocated by topic.€ť How can I label the columns so my code could be: Cells(LINENUM, email) = InputBox(Prompt:="What is the e-mail €¦.. Ive tried naming the column using Range and it was called €śemail€ť. But that crashes the macro. Any help would be appreciated! |
Find data for a Macro with a Dynamic Range
Assuming you have your column labels (e.g,. "Name", "Address", "Email", etc)
in cells A1:H1, use code like Sub PromptForInput() Dim RowNum As Long Dim S As String Dim Col As Variant RowNum = ActiveCell.Row ' or whatever S = "ADDRESS" ' or whatever Col = ColNum(S) If IsError(Col) = True Then MsgBox "Invlaid Column Identifier" Exit Sub Else Cells(RowNum, Col).Value = InputBox("Enter " & S) End If End Sub Function ColNum(Label As String) As Variant Dim V As Variant V = Application.Match(Label, Range("A1:E1"), 0) If IsError(V) = True Then ColNum = CVErr(xlErrRef) Else ColNum = CLng(V) End If End Function -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mathew" wrote in message ... I have data with numerous records. Each row is a record. Users add rows about 2 times a week. The columns are organized so that particular data goes under them, like job title, or email. What I have completed is a Macro allows the users to input the data into the database. Im using input boxes and it works fine. I used Input box, code is: Cells(LINENUM, 24) = InputBox(Prompt:="What is the e-mail for the Certified Accountant ?" & vbNewLine & "Please type it below! ", Title:="E-mail for the Certified Accountant of " & FSName, Default:=CATitle) It worked great! However, it seems that the data will have an occasional column added. I cant predict where the column will be added or when that will occur. There are a great number of columns and the users want to keep the columns €ślocated by topic.€ť How can I label the columns so my code could be: Cells(LINENUM, email) = InputBox(Prompt:="What is the e-mail €¦.. Ive tried naming the column using Range and it was called €śemail€ť. But that crashes the macro. Any help would be appreciated! |
Find data for a Macro with a Dynamic Range
chip: thanks. Great suggestion!
"Chip Pearson" wrote: Assuming you have your column labels (e.g,. "Name", "Address", "Email", etc) in cells A1:H1, use code like Sub PromptForInput() Dim RowNum As Long Dim S As String Dim Col As Variant RowNum = ActiveCell.Row ' or whatever S = "ADDRESS" ' or whatever Col = ColNum(S) If IsError(Col) = True Then MsgBox "Invlaid Column Identifier" Exit Sub Else Cells(RowNum, Col).Value = InputBox("Enter " & S) End If End Sub Function ColNum(Label As String) As Variant Dim V As Variant V = Application.Match(Label, Range("A1:E1"), 0) If IsError(V) = True Then ColNum = CVErr(xlErrRef) Else ColNum = CLng(V) End If End Function -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Mathew" wrote in message ... I have data with numerous records. Each row is a record. Users add rows about 2 times a week. The columns are organized so that particular data goes under them, like job title, or email. What I have completed is a Macro allows the users to input the data into the database. Im using input boxes and it works fine. I used Input box, code is: Cells(LINENUM, 24) = InputBox(Prompt:="What is the e-mail for the Certified Accountant ?" & vbNewLine & "Please type it below! ", Title:="E-mail for the Certified Accountant of " & FSName, Default:=CATitle) It worked great! However, it seems that the data will have an occasional column added. I cant predict where the column will be added or when that will occur. There are a great number of columns and the users want to keep the columns €ślocated by topic.€ť How can I label the columns so my code could be: Cells(LINENUM, email) = InputBox(Prompt:="What is the e-mail €¦.. Ive tried naming the column using Range and it was called €śemail€ť. But that crashes the macro. Any help would be appreciated! |
All times are GMT +1. The time now is 06:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com