Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range Name Macro | Excel Discussion (Misc queries) | |||
dynamic range based on criteria, within a dynamic range, passed to a function | Excel Programming | |||
creating a dynamic range based on criteria, within a dynamic range, and passing it to a function | Excel Programming | |||
Using a macro to add a count and sum on a dynamic range | Excel Programming | |||
Sum on Dynamic Data Range - Macro | Excel Programming |