Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

To display values in correct columns in Excel

Status
Not open for further replies.

Newbie311

Programmer
Oct 6, 2003
30
GB
Hi there, the below code is being used to retrieve transport details from a text file onto an excel spreadsheet. The problem i have is that theres more than 1 transport detail in some files,i need to display these on the same line in the column beside it....Can anyone help?
Thanks


Example of what code does:
00-11-23-45-11-dd
00-11-23-45-31-dd
00-11-23-45-81-dd



For i = 0 To 4
If InStr(1, GetLine, "Transport:") > 0 Then 'Search for Network Card
If InStr(1, GetLine, "00-00-00-00-00-00") > 0 Then
Debug.Print "No Card: " & GetLine
Line Input #FF, GetLine
ActiveCell.Offset(e, 4).Value = Right(GetLine, Len(GetLine) - InStr(1, GetLine, ",") + 1)
ActiveCell.Offset(e, 4).Value = Mid(ActiveCell.Offset(e, 4).Value, 3, 17)
If ActiveCell.Offset(e, 4).Value = "" Then ActiveCell.Offset(e, 4).Value = "No Card"
e = e + 1
Else
Debug.Print "Card: " & GetLine
ActiveCell.Offset(e, 4).Value = Right(GetLine, Len(GetLine) - InStr(1, GetLine, ",") + 1)
Debug.Print "Right: " & ActiveCell.Offset(e, y).Value
ActiveCell.Offset(e, 4).Value = Mid(ActiveCell.Offset(e, 4).Value, 3, 17)
Debug.Print "Mid: " & ActiveCell.Offset(e, 4).Value

e = e + 1

End If
End If
Exit For
Next i
 
Hi newbie,

Do NOT put these values in separate columns!

The same data items with same/different values belong in a common column.

However, you can REPORT your data in the way you describe, using a PivotTable by placing is heading in the COLUMN AREA.

NO CODE REQUIRED! :)

Skip,
Skip@TheOfficeExperts.com
 
For the purpose of this application im afraid i need them in seperate columns.Im reading in multiple files, each file may have more than one transport detail which needs to be displayed in a seperate column.
I.E if you have 3 different independent values in 1 file which relate
to the same record they need to displayed seperately along side it...
thanks for your help
 
Example of what code does:
00-11-23-45-11-dd
00-11-23-45-31-dd
00-11-23-45-81-dd

if to results are obtained from the same file then they need by be written like;
1-00-11-23-45-11-dd
2-00-11-23-45-31-dd 00-11-23-45-81-dd 00-11-23-45-81-dd
3-00-11-23-45-81-dd

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top