jaminben
02-06-2012, 06:17 PM
Hello,
First time poster and first time coder... not a good combination :(
Anyway, I'm trying to auto populate a sheet in excel with data from other worksheets within the same workbook. I've gotten the sheet to populate just fine (well the best I can with my knowledge) but one of the cells I need to get data from contains two values seperated by a "/". Anyone got any ideas how I can seperate the values so they can be used individually?
My code so far looks like:
Sub PopulateFedFromSheet()
Dim ws As Worksheet
' Check worksheet for name
IsBoardSchedule = "BOARDSCHEDULE"
' Cell row variable
i = 8
' Check each worksheet within workbook
For Each ws In ActiveWorkbook.Worksheets
' Will continue if an error results
On Error Resume Next
' Check to see if sheet is a board schedule
If ws.Range("A1") = IsBoardSchedule Then
' Increment Cell Row Number
i = i + 1
' Sheet name
RowRange = "B" & i
BoardName = ws.Range("B6")
Sheets("FED FROM").Select
Range(RowRange) = BoardName
Debug.Print BoardName
' Board location
RowRange = "C" & i
LocationName = ws.Range("H6")
Sheets("FED FROM").Select
Range(RowRange) = LocationName
Debug.Print LocationName
' Fed From
RowRange = "F" & i
FedFrom = ws.Range("O6")
Sheets("FED FROM").Select
Range(RowRange) = FedFrom
Debug.Print FedFrom
' Fuse MCB type
RowRange = "G" & i
FuseType = ws.Range("Q8")
Sheets("FED FROM").Select
Range(RowRange) = FuseType
Debug.Print FuseType
' Fuse rating
RowRange = "H" & i
FuseRating = ws.Range("S8")
Sheets("FED FROM").Select
Range(RowRange) = FuseRating
Debug.Print FuseRating
' Loop reading
RowRange = "D" & i
LoopReading = ws.Range("Y6")
Sheets("FED FROM").Select
Range(RowRange) = LoopReading
Debug.Print LoopReading
' PSC reading
RowRange = "E" & i
PSCReading = ws.Range("Y8")
Sheets("FED FROM").Select
Range(RowRange) = PSCReading
Debug.Print PSCReading
End If
Next ws
End Sub
The part that needs splitting comes from ' Fuse MCB type and could look something like:
60898/32 or 60898\32
And I need the 60898 value and then the 32 value individually.
Also seeing as this is my first time coding... how does the above code look? Is it poorly writen, could it be improved?
Many Thanks in advance
Ben
First time poster and first time coder... not a good combination :(
Anyway, I'm trying to auto populate a sheet in excel with data from other worksheets within the same workbook. I've gotten the sheet to populate just fine (well the best I can with my knowledge) but one of the cells I need to get data from contains two values seperated by a "/". Anyone got any ideas how I can seperate the values so they can be used individually?
My code so far looks like:
Sub PopulateFedFromSheet()
Dim ws As Worksheet
' Check worksheet for name
IsBoardSchedule = "BOARDSCHEDULE"
' Cell row variable
i = 8
' Check each worksheet within workbook
For Each ws In ActiveWorkbook.Worksheets
' Will continue if an error results
On Error Resume Next
' Check to see if sheet is a board schedule
If ws.Range("A1") = IsBoardSchedule Then
' Increment Cell Row Number
i = i + 1
' Sheet name
RowRange = "B" & i
BoardName = ws.Range("B6")
Sheets("FED FROM").Select
Range(RowRange) = BoardName
Debug.Print BoardName
' Board location
RowRange = "C" & i
LocationName = ws.Range("H6")
Sheets("FED FROM").Select
Range(RowRange) = LocationName
Debug.Print LocationName
' Fed From
RowRange = "F" & i
FedFrom = ws.Range("O6")
Sheets("FED FROM").Select
Range(RowRange) = FedFrom
Debug.Print FedFrom
' Fuse MCB type
RowRange = "G" & i
FuseType = ws.Range("Q8")
Sheets("FED FROM").Select
Range(RowRange) = FuseType
Debug.Print FuseType
' Fuse rating
RowRange = "H" & i
FuseRating = ws.Range("S8")
Sheets("FED FROM").Select
Range(RowRange) = FuseRating
Debug.Print FuseRating
' Loop reading
RowRange = "D" & i
LoopReading = ws.Range("Y6")
Sheets("FED FROM").Select
Range(RowRange) = LoopReading
Debug.Print LoopReading
' PSC reading
RowRange = "E" & i
PSCReading = ws.Range("Y8")
Sheets("FED FROM").Select
Range(RowRange) = PSCReading
Debug.Print PSCReading
End If
Next ws
End Sub
The part that needs splitting comes from ' Fuse MCB type and could look something like:
60898/32 or 60898\32
And I need the 60898 value and then the 32 value individually.
Also seeing as this is my first time coding... how does the above code look? Is it poorly writen, could it be improved?
Many Thanks in advance
Ben