Find data for a Macro with a Dynamic Range

[Mathew]

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. I’m 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
can’t 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 …..

I’ve tried naming the column using Range and it was called “emailâ€. But
that crashes the macro. Any help would be appreciated!

 

[Chip Pearson]

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]

chip: thanks. Great suggestion!

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)