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]

Rather than looping through all the cells, have you considered using the find/replace function?

Explain in words what it is you're trying to do and we'll see if this is a more applicable method

 

[VBABEGINER]

Dear Sir,

Thank You for the suggestion.

Sir, In the code i mentioned whai is the requirement
with ' commented lines.

i hope dis will explainable wht i required?

I want to find "Circuits:" this is col A (anywhere).
function should start from row 13 col A.
cell(13,1) copy in cell(13,2)
like
cell(,1) in cell(,2)
note - in every report "Circuits:" we can find after this last cell + 2
i.e position of "Circuits:" = this last cell(,1) in cell(,2) + 2

once col A data copy into col B,
my cell (10, 1) have one value.
if this,
cell(,1) in cell(,2) = "Circuits:" then

each cell in col B started from cell 13,
copy - cell (10, 1) value til the same, cell(,1) in cell(,2) , B col

therefore, if u can see in code, i put a value for this,
cell(,1) in cell(,2) = p

This is the problem.

Hope this will certainly help you Sir to debbug.

 

[Weaver]

Do you have any sample data, and what you expect it to look like afterwards?

How many times do you expect "circuits:" to appear in column A? If it's only once, this is probably easier than I thought.

Sub test()
    Set r = Cells.Find(what:="circuits:", MatchCase:=False)
    r.Offset(, 1).Value = r
    v = Range("A10").Value
    if v <> "" then Range("B13", r.Offset(-1, 1)).Value = v
End Sub

 

[Weaver]

Probably should be this, in case 'circuits:' appears elsewhere in the sheet other than column A

Sub test()
    Set r = Columns(1).Find(what:="circuits:", MatchCase:=False)
    r.Offset(, 1).Value = r.Value
    v = Range("A10").Value
    If v <> "" Then Range("B13", r.Offset(-1, 1)).Value = v
End Sub

 

[VBABEGINER]

Sir, pls wait. let me try this, i will tell you.

 

[VBABEGINER]

No..No.
This copy b10 and paste col b from range 13 has done already from mine.
Next step i am traying,
col A data started from range 13.
'circuits:' in col A is uncertain to find.
but 'circuits:' we can find by the last row of value + 2 row
after all col a data copy to col B, then lastly, will get this 'circuits:' value as above discussed.
Then range b10 have some value this value got copied into col A.

Sorry - Sorry.

range b 10 value should get copy in col A. Right.
I am sorry Sir, mistakly provide you the wrong information.
Prev i told b10 value copy to col b.
My mistake.

 

[VBABEGINER]

Yep.
This query getting any one or not.
for explaning me more.

 

[Weaver]

So:

1. Find the row where Column A has the word 'circuits'
2. not sure what you mean by "but 'circuits:' we can find by the last row of value + 2 row" Are you saying you want to find the row that is +2 rows below the row containing 'circuits'?
3. All data in column A is copied to column B
4. if the value in cell A10 is not blank then all of column A down to the row containing the word 'circuits' (or this row +2?) should be replaced by the value in A10.

Am I close?

 

[VBABEGINER]

Good Morning Sir n All,
Yes, Even i am stiill trying.