I'm building a template that gets all needed input from an excel file that is selected by the user.
These excel files that the user selects generally have the same layout, however the data cannot be found on the exact same spot and each file contains random column merges, which makes it impossible to hard code the locations of the needed data. So I did create some loops, but these also do not work 100%.
To give you an example I have this user inputted excel file:
From this I need to retrieve the name of all card holders. I have written the following code to load these names into my template. It looks for the word "Naam" in a range in row 4 and then offsets this to get the names.
Sub KlantInformatie(wsTemplate, wsKlantprofiel)
Dim i, j As Range
'Inladen accountnummer
wsTemplate.Range("antAccountnummer").Value = wsKlantprofiel.Range("B2").Value
'Zoeken en inladen van namen CH en ECH's
For Each i In wsKlantprofiel.Range("C4:K4").Cells
If i.Value = "Naam" Then
With wsTemplate
.Range("antNaamCH") = i.Offset(, 1).Value
.Range("antNaamECH1") = i.Offset(, 6).Value
.Range("antNaamECH2") = i.Offset(, 10).Value
.Range("antNaamECH3") = i.Offset(, 11).Value
.Range("antNaamECH4") = i.Offset(, 12).Value
.Range("antNaamECH5") = i.Offset(, 13).Value
.Range("antNaamECH6") = i.Offset(, 14).Value
.Range("antNaamECH7") = i.Offset(, 15).Value
.Range("antNaamECH8") = i.Offset(, 16).Value
.Range("antNaamECH9") = i.Offset(, 17).Value
.Range("antNaamECH10") = i.Offset(, 18).Value
End With
End If
Next i
However these offsets are not always correct because the data could be in a different column. So what I think I need is a code that offsets to the next non empty value. But I'm not sure how to do that.