I have some strings:
dim sAttribute() as string, n as integer
sAttribute(1) = "tvoc_ppb"
sAttribute(2) = "p1_ppm"
sAttribute(3) = "p2.5_ppm"
sAttribute(4) = "p3_ppm"
sAttribute(5) = "rh"
sAttribute(6) = "ws"
sAttribute(7) = "wd"
n = 6
I have a datasheet from an outside source that outputs data with the attribute names within the numeric data itself (smh).On top of this, the data headers are not even consistent, and some sources have 1 range of data and others have multiple columns, so I've been tasked on munging and compiling all these disparate sources into one table. Luckily they're all csv files. I remove the alpha characters once I've identified the address of the data range, but I've been trying to use Instr and Like to locate the first instance using the predefined sAttribute() values. Once I have the address, I can then remove the string variables from the numeric data easily and got that working fine; however, I can't get my initial procedure to match the partial string. For example, a data source comes with a 'ppb' suffix attached to all the data and I want to look the first instance of sAttribute(1). I've tried using wildcards and it still doesn't work.
dim ws as worksheet, lCol as long, i as long, j as long, k as long, n as integer,
dim fRng as range, rng as range, fStrg as long
With ws
lCol= LastCol(ws) '<--------function not in example
For i = 1 To lCol
For j = 1 To 2 '<---------------look through attribute headers first then first row of data for an instance of sAttribute()
For k = 1 To n '<-------------loop through defined strings
Set fRng = .Cells(j, i)
If "*" & LCase(fRng.Value) Like sAttribute(k) Then
' Set fRng = rng.Find(What:=sAttribute(k), After:=rng(1))
If Not fRng Is Nothing Then
fStrg = Split(fRng.Address, "$")(2)
'do stuff
Exit For
End If
End If
Next
Next
Next
End With
I've also tried using with no success:
If InStr(LCase(fRng.Value), LCase(sAttribute(k))) > 0 Then
Bookmarks