PDA

View Full Version : Using StringIndexOf to return two values



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

Kenneth Hobs
02-06-2012, 07:15 PM
Welcome to the forum!

First off, let's see if we can modify your macro recorder type of syntax to something that is faster, easier to maintain, and easier to code.

For this snippet:
RowRange = "G" & i
FuseType = ws.Range("Q8")
Sheets("FED FROM").Select
Range(RowRange) = FuseType
Debug.Print FuseType
I would do this kind of thing:
' Check each worksheet within workbook
Dim ws As Worksheet, wst As Worksheet, i As Long
Set wst = Sheets("FED FROM")
For Each ws In ActiveWorkbook.Worksheets
i = i + 1
With ws
wst.Range("G" & i).Value2 = .Range("Q8").Value2
Debug.Print .Range("Q8").Value2
End With
Next ws
In my next post, I will show an example on how to Split() a value.

I do recommend that you try not to use On Error. We can check to see if a cell is locked if that is an issue. We can check to see if a worksheet exists if needed.

Kenneth Hobs
02-06-2012, 07:28 PM
Sub Test_Split()
Dim s() As String, aString As String

aString = "85 / 100"
'aString = "85 * 100"
Worksheets("Sheet1").Range("A1").Value2 = aString

s() = Split(aString, "/")
With Worksheets("Sheet2")
If UBound(s) = 1 And InStr(aString, "/") > 0 Then
.Range("A1").Value2 = Trim(s(0))
.Range("A2").Value2 = Trim(s(1))
Else
.Range("A1").Value2 = Trim(s(0))
.Range("A2").Value2 = ""
End If
End With
End Sub

jaminben
02-06-2012, 07:34 PM
Cool, thanks for those :beerchug:

Its going to take me awhile to workout what you've done but I'll let you know how I get on.

Thankyou

mikerickson
02-06-2012, 11:03 PM
One can avoid branching
S() = Split(aString & "/" , "/")

.Range("A1").Value2 = Trim(s(0))
.Range("A2").Value2 = Trim(s(1))

jaminben
02-07-2012, 04:29 AM
Thanks very much guys... I got it to work using:

Sub PopulateFedFromSheet()
Dim ws As Worksheet
Dim s() As String, aString As String

' Check worksheet for name
IsBoardSchedule = "BOARDSCHEDULE"
' Cell row variable
i = 8

' Check each worksheet within workbook
For Each ws In ActiveWorkbook.Worksheets

' 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
FuseType = ws.Range("Q8")
RowRange1 = "G" & i
RowRange2 = "H" & i
aString = FuseType
FuseType = aString

s() = Split(aString & "/", "/")

With Worksheets("FED FROM")

.Range(RowRange1).Value2 = Trim(s(0))
.Range(RowRange2).Value2 = Trim(s(1))

End With
' 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

I tried to add the other code snippet from your example but couldn't get it to work correctly. I'll come back to that part at a later date to see if I can make it work.

Thankyou

Ben