Using column name instead of number ...

adambc

Active Member
Joined
Jan 13, 2020
Messages
368
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I know that there are previous posts about this subject, but I can't fathom out how to use the various solutions to fix my problem!

I have a Worksheet which is populated with new records via a UserForm - and updated by reading a record into another (similar) UserForm which is then saved back to the Worksheet ...

I've posted the code that does the (new record) Save below (the update UserForm uses similar code, but uses the existing record row number), which uses column numbers, but the problem I now have is that user requirements keep adding new columns/re-ordering the columns, which involves working through the code one-by-one every time there's a change!!!

VBA Code:
Private Sub cmdSave1_Click()

Dim m As Long

m = Cells(Rows.Count, 1).End(xlUp).Row

Cells(m + 1, 1).value = txtIncidentID
Cells(m + 1, 2).value = txtIncidentDate
Cells(m + 1, 3).value = txtIncidentTimeHours & ":" & txtIncidentTimeMins
Cells(m + 1, 4).value = txtReportedDate
Cells(m + 1, 5).value = txtReportedTimeHours & ":" & txtReportedTimeMins
Cells(m + 1, 6).value = txtReportedBy
Cells(m + 1, 7).value = cboStatus
Cells(m + 1, 8).value = cboDepartment
Cells(m + 1, 9).value = cboIncidentType
Cells(m + 1, 10).value = cboIncidentNature
Cells(m + 1, 11).value = cboIncidentFormat
Cells(m + 1, 12).value = cboNearMiss
Cells(m + 1, 13).value = cboClinicalData
Cells(m + 1, 14).value = txtIncidentSummary
Cells(m + 1, 15).value = cboDatSubjectAdvised
Cells(m + 1, 16).value = txtRemedialActionsTaken
Cells(m + 1, 17).value = txtRemedialActionsPlanned
Cells(m + 1, 18).value = txtEvidence
Cells(m + 1, 19).value = cboImpact
Cells(m + 1, 20).value = cboLikelihood

End Sub

What I'd like to do is NAME columns (I don't know whether that's possible) and then use the NAME instead of the column number - WITHOUT REFERENCING THE COLUMN NUMBER AT ANY TIME ...

Is that possible???

Many thanks ...
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi
Perhaps
VBA Code:
Cells(m + 1, 1)=Cells(m + 1, "A")
Cells(m + 1, 2)=Cells(m + 1, "B")
 
Upvote 0
Hhhmmm, thanks, but not sure that works ...

Using the first 3 columns as an example ...

Say I have a user requirement to add a new column inbetween Incident ID and Incident Date - and move Incident Time before Incident Date - the revised code would look like ...

VBA Code:
Cells(m + 1, 1).value = txtIncidentID
Cells(m + 1, 2).value = txtNewColumn
Cells(m + 1, 3).value = txtIncidentTimeHours & ":" & txtIncidentTimeMins
Cells(m + 1, 4).value = txtIncidentDate

But ... I would also have to adjust column number for every other column manually to take into account the new column ...

However, if I could somehow assign columns with a name, the before/after code would look (something) like this ...

VBA Code:
'BEFORE

Cells(m + 1, IncidentID).value = txtIncidentID
Cells(m + 1, IncidentDate).value = txtIncidentDate
Cells(m + 1, IncidentTime).value = txtIncidentTimeHours & ":" & txtIncidentTimeMins

'AFTER

Cells(m + 1, IncidentID).value = txtIncidentID
Cells(m + 1, NewColumn).value = txtNewColumn
Cells(m + 1, IncidentTime).value = txtIncidentTimeHours & ":" & txtIncidentTimeMins
Cells(m + 1, IncidentDate).value = txtIncidentDate

... I could "just" add the new column into the code and any other changes would be handled automatically ...

Is that possible?

Thanks again ...
 
Upvote 0
You could use Application.Match to find the column numbers.

For example, assuming the headers are in row 1 on the active sheet, you could use something like this to get the column number for the 'Incident ID'.
VBA Code:
Res = Application.Match("Incident ID", Rows(1), 0)

Another approach could be to create a dictionary of the headers
Code:
Dim dicCols As Object
Dim rng As Range

    Set dicCols = CreateObject("Scripting.Dictionary")

    Set rng = Cells(1, 1)

    Do
        dicCols(rng.Value) = rng.Column
        Set rng = rng.Offset(, 1)
    Loop Until rng.Value = ""

You could use the dictionary like this.
VBA Code:
Cells(m+1, dicCols("Incident ID").Value = txtIncidentID
 
Upvote 0
Well
you may try this method maybe you can sort it out
VBA Code:
Dim p, i
    Dim a, b As Variant
    ReDim a(1 To Cells(1, Columns.Count).End(xlToLeft).Column)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a)
        b(i) = Mid(Cells(1, i).Address, 2, 1)
    Next
    For i = 1 To UBound(a)
        If a(i) = "IncidentTime" Then
            Cells(m + i, a(i)).Value = TextBox & xxx & ":" & TextBox & xxx
        Else
            If a(i) = "ReportedTimeHours" Then
                Cells(m + i, a(i)).Value = TextBox & yyy & ":" & TextBox & yyy
            Else
                Cells(m + i, a(i)).Value = TextBox & i
            End If
        End If
    Next
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top