Using column name instead of number ...

[adambc]

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!!!

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 ...

 

[mohadin]

Hi
Perhaps

Cells(m + 1, 1)=Cells(m + 1, "A")
Cells(m + 1, 2)=Cells(m + 1, "B")

 

[adambc]

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 ...

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 ...

'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 ...

 

[Norie]

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'.

Res = Application.Match("Incident ID", Rows(1), 0)

Another approach could be to create a dictionary of the headers

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.

Cells(m+1, dicCols("Incident ID").Value = txtIncidentID

 

[mohadin]

Well
you may try this method maybe you can sort it out

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