using a lookup, or somehow to assign variables
using a lookup, or somehow to assign variables
(OP)
I'm trying to learn how to get more familiar with code writing using VBA to start.
I've got a data file that I import to Excel, I've got it setup to lay it out pretty nice for my liking but I'm trying to expand.
I have a cell, A1 that has a few numbers that refer to parameters, say 1 11 23 where 1 = Bob 11 = Rick 23 = Jeff.
I want to put this into my Macro script so that depending on what numbers are in Cell A1, it will output these names and then place them into a specific column and the data then in the column belongs to the name above it.
Hopefully this makes sense, anyone have any suggestions, or other options on how to do it. I may have about 20 different numbers that equal names, but starting off small.
Thanks!
I've got a data file that I import to Excel, I've got it setup to lay it out pretty nice for my liking but I'm trying to expand.
I have a cell, A1 that has a few numbers that refer to parameters, say 1 11 23 where 1 = Bob 11 = Rick 23 = Jeff.
I want to put this into my Macro script so that depending on what numbers are in Cell A1, it will output these names and then place them into a specific column and the data then in the column belongs to the name above it.
Hopefully this makes sense, anyone have any suggestions, or other options on how to do it. I may have about 20 different numbers that equal names, but starting off small.
Thanks!





RE: using a lookup, or somehow to assign variables
Do you have any code written for this, or is this just someting you're kicking around? Where does your master list lie, i.e., 1=Bob, 11=Rick etc., is it on another sheet, something you're going to hard-code in your macro, text files maybe?
Let us know, we might be able to better help you out.
Todd
RE: using a lookup, or somehow to assign variables
From then on is where I am now, I know what I want it to do, just not sure of how to build it and what the commands are. The log file is just that, a log file, so these numbers 1 11 23 where 1 = Bob 11 = Rick 23 = Jeff will change depending on what parameters I am logging, so I'll eventually need to get corresponding names for each parameter, but for now just 3 or so would be good enough to get me going, then I can expand the format and add more data...
RE: using a lookup, or somehow to assign variables
This sounds like you might want to look into ADO recordsets and creating recordsets "on the fly", and then look into filtering your recorsets...
But then if you've only got 20 or so, you may look into Arrays and/or Hash Tables - these can be a little complex but might get you what you need without all the overhead of ADO.
HTH
Todd
RE: using a lookup, or somehow to assign variables
Thanks for the info though.
RE: using a lookup, or somehow to assign variables
RE: using a lookup, or somehow to assign variables
Attached is a working example. Since you didn't supply a sample formatting of your log text file, I made one up as well.
brengine
RE: using a lookup, or somehow to assign variables
Thanks for the help though!!!
RE: using a lookup, or somehow to assign variables
RE: using a lookup, or somehow to assign variables
RE: using a lookup, or somehow to assign variables
RE: using a lookup, or somehow to assign variables
Looks like you're on your way. Here's just a minor suggestion:
CODE
ChDir "C:\where_ever_you_save\macro"
NewFN = Application.GetOpenFilename(FileFilter:="Text Files (*.log), *.log", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
End IF
Workbooks.OpenText Filename:=NewFN, Origin:= _
xlWindows, StartRow:=3, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers _
End Sub
HTH
Todd
RE: using a lookup, or somehow to assign variables
The log file in my zip is the actual raw data that I get. I want to format it to look like the example in Book2.xls. The issue is making the conversion from each number variable to the name, Spark and putting it up there like shown...
RE: using a lookup, or somehow to assign variables
Params =
2 5 6
9 30 32
76 77 86
Return =
A B C
D E F
G H I
My Range is A1-F1, so once it matches A1 and returns to A2, then move on to B1 until matching the corresponding Return.
If A1 = 2, then return A in cell A2, otherwise keep searching until if finds one of the params in A1 to match a name to, if none exist then leave blank....
RE: using a lookup, or somehow to assign variables
I think I get what you want to do. The problem is, you need to know more about your log file structure, I've been trying to see it, but don't, but running the code below works to import the data.
CODE
ChDir "C:\Scratch\macro\log file"
NewFN = Application.GetOpenFilename(FileFilter:="Text Files (*.log), *.log", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
End If
Workbooks.OpenText Filename:=NewFN, Origin:=xlWindows, StartRow:=3, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
End Sub
If the data is coming in correctly, then using a two step approach might be the easiest way to go. Let Excel do some of the work for you. I'm guessing column A is your trigger, once the data is in, it would just be a matter of reprocssing it, but this time, instead of parsing a file, you parse the rows and columns, sort your data how you want on another sheet. That might look somehting like this:
CODE
Dim wrks As Workbooks
Dim wrkImp As Workbook ' Imported workbook
Dim shtImp As Worksheet ' Imported worksheet
Dim rngImp As Range ' Imported range
Dim rngCll As Range ' Imported cell in range
Dim sTrigger As String ' Imported cell value/text
Dim shtNew As Worksheet ' Target or new worksheet
Dim rngNew As Range ' Target range
Dim rngNewCll As Range ' Target cell
' Dim NewFN As String ' Log file to open
ChDir "C:\Scratch\macro\log file"
NewFN = Application.GetOpenFilename(FileFilter:="Text Files (*.log), *.log", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
End If
Workbooks.OpenText Filename:=NewFN, Origin:=xlWindows, StartRow:=3, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
' Now process the data.
'
Set wrkImp = Application.Workbooks(Workbooks.Count + 1)
Set shtImp = wrkImp.ActiveSheet
Set rngImp = shtImp.Range("A1").CurrentRegion
For Each rngCll In rngImp
If rngCll.Column = 1 And rngCll.Text <> sTrigger Then
' Check for new value in "A"
'
Debug.Print rngCll.Text
End If
If rngCll.Column = 1 Then
' Collect and store value in Column A Row n...
'
sTrigger = rngCll.Text
End If
Next rngCll
End Sub
This is just off the top of my head, but you get the idea.
HTH
Todd
RE: using a lookup, or somehow to assign variables