Find n replace

[VBABEGINER]

Hi All,

Can any one pls help for debbugung this.

Public Function FindBus()
Dim cnt As Long
Dim rw As Long
Dim p As Long

' rw counting all rows in col A
rw = Range("A" & Rows.Count).End(xlUp).Row
For cnt = 13 To rw

' "Circuits:" = in col A anywhere
' finding "Circuits:"
If Cells(cnt, 1) = "Circuits:" Then

' Assign p = the count of rows till the "Circuits:" is found.
p = Range(Cells(cnt, 1) & Rows.Count)

' cp is another variable.
' cp = row 13 to value p (count of rows till the "Circuits:" is found)
For cp = 13 To p
If Cells(10, 1).Value <> "" Then

' if above condtion is true then
' (count of rows till the "Circuits:" is found), col 1
' copy and paste it into cells(from b13,2)
Cells(cp, 1).Value = Cells(cnt, 2).Value

'Cells(cnt, 2) = Right(Cells(10, 1), 11)
'If Cells(cnt + 2, 1) = "Circuits:" Then
'p = cnt

'Exit Function
'Else
' Cells(cnt, 2) = Right(Cells(10, 1), 11)
'End If
'End If
End If
Next
Next
End Function

 

[Weaver]

If you could clear up point 2, I think we could get closer.

 

[VBABEGINER]

Let me present my data, Finally.

COL B contains no data

col A ............................col B

10 PQR: LS-0101-04P
11
12
13 ABC-1
14 PQR-1
15 EWR-3
16 POE-34
17 POW-34
18 NJAS-76
19 POO-78
20 NAG-12
21 no data
22 no data
23 Circuits Totals:

Explaination - First the data available in Col A.
This should copy into col B.

after copy into col B.

col A ..........................col B

10 PQR: LS-0101-04P
11
12
13 .............................ABC-1
14 .............................PQR-1
15 .............................EWR-3
16 .............................POE-34
17 .............................POW-34
18 .............................NJAS-76
19 .............................POO-78
20 .............................NAG-12
21 no data
22 no data
23 Circuits Totals:

and o/p should be like this -

col A ..........................col B

10 PQR: LS-0101-04P
11
12
13 LS-0101-04P............ABC-1
14 LS-0101-04P............PQR-1
15 LS-0101-04P............EWR-3
16 LS-0101-04P............POE-34
17 LS-0101-04P............POW-34
18 LS-0101-04P............NJAS-76
19 LS-0101-04P............POO-78
20 LS-0101-04P............NAG-12
21 no data
22 no data
23 Circuits Totals:

After This, data in cell (10,1) is nothing but "PQR: LS-0101-04P"

should get copy into col A till the value -

"20 NAG-12" this last cell.


Now i hope, this is much better explanation.

Now what i coded is the following -
Pls any one help out to solve this,

Public Function FindBus()
Dim cnt As Long
Dim rw As Long
Dim p As Long
Dim ct As Long

Cells(7, 1) = "Bus Item Tag"
Cells(7, 2) = "Load Name"

rw = Range("A" & Rows.Count).End(xlUp).Row
For ct = 13 To rw
For cnt = 13 To rw

Cells(cnt, 2) = Cells(cnt, 1)

If Cells(cnt, 2) <> "" Then
Cells(cnt, 1) = ""
End If

If Cells(cnt + 2, 1) = "Circuits:" Then

Cells(ct, 1) = Right(Cells(10, 1), 11)

If Cells(ct + 2, 1) = "Circuits:" Then
Exit Function
Else
Cells(ct, 1) = Right(Cells(10, 1), 11)
End If

End If

Next cnt
Next ct
End Function

 

[VBABEGINER]

Can Any one look at this, need some master help.
From last 2 days i am facing this problem.
I got help from Weaver Member, but still not did.