Solved: Search Text and loop the same thing for same text

[bvsramesh]

Hi Team,

I am attaching a file along with this and i need that....

1) from the oringinal sheet a text should be search (Balance Pool = ) if it is found and copy all the data in the row

for eg: Balance Pool = D0CB VEENA LTD

Balance Pool = find it should copy all the text in row "Balance Pool = D0CB VEENA LTD"

this should be loop up to the last row of the sheet. and the above will be copied before the product code and shold be CONCATENATE with all product codes.

plz find in the sheet for your ready reference.

But my problem is i have started with finding the text but need ur help to loop it up the last row when ever it found the Specific Text and it should be copied



[VBA]Sub FindLastRow()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="Balance Pool = ", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End If
End Sub
[/VBA]


Please help as i am trying but i did't get any idea so far....


Thanks in advance.

BVSR.

[Bob Phillips]

[vba]


Public Sub PorocessData()
Dim lastrow As Long
Dim i As Long, ii As Long

With Worksheets("Original")

.Columns("A").Insert
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To lastrow

If .Cells(i, "B").Value <> "" Then

ii = i + 1
Do

ii = ii + 1
.Cells(ii, "A").Value = .Cells(i, "B").Value
If .Cells(ii, "B").Value <> "Product Code" Then

.Cells(ii, "A").Value = .Cells(ii, "A").Value & .Cells(ii, "B").Value
End If
Loop Until .Cells(ii + 1, "B").Value = ""

i = ii
End If
Next i

.Columns("A").AutoFit
End With
End Sub
[/vba]

[bvsramesh]

Hi,

Than you very Much... and this is running good.. but can i ask you to explain me the the above code how it's working. Because you have not using any functions like Concatenate kind of function in the code. If you explain me next time i can take this example in my further projects also.

Once again than you... and thakns for this Forum.

Regards,

BVSR

[Bob Phillips]

I am using concatenate, look at the row of code that goes furthest to the right, I am concatenating the value in column B of the current row with the value in column B of the header row - the & is the concatenate operator.

[bvsramesh]

Hi,

Ya I got it now and i understand how it works... thanks for every thing..

Regards,

BVSR

[bvsramesh]

Hi Xld / Team,

I have struck again in the below. as i am supposed to vlookup with the previous day's file with the same sheet after above changes have made ( the Previous code was given for the above changes) . for that i have copied the previous day's file sheet and insert a new sheet (Sheet1) in the present day's file and pasted it there.

From that i am trying to vlookup the extra columns (J and K) based on the "A" Column.

But if i give the below code (as an example which you have provided me earlier) it giving only Ture or False but not giving the values from the Sheet1.

Please guide me if i am wrong. plz find the code.


[vba]Public Sub Stock_OOP_302()
Dim lastrow As Long
Dim i As Long, ii As Long

With Worksheets("Original")

.Columns("J:K").Insert

' to get the borders like the previous columns

With Columns("I:I").Select
Selection.Copy
Columns("J:K").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With


lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To lastrow

If .Cells(i, "B").Value <> "" Then

ii = i + 1
Do

ii = ii + 1
.Cells(ii, "J").Value = ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[ii],Sheet1!RC[ii],10,0)"
.Cells(ii, "K").Value = ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[ii],Sheet1!RC[ii],11,0)"
If .Cells(ii, "B").Value <> "Product Code" Then
'ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[ii],Sheet1!RC[ii],10,0)"
'ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[ii],Sheet1!RC[ii],11,0)"

End If
Loop Until .Cells(ii + 1, "B").Value = ""

i = ii
End If
Next i

.Columns("A").AutoFit
End With
End Sub

[/vba]

[Bob Phillips]

Doesn't put anything there for me. is this on the post-process worksheet, or pre-process?

[bvsramesh]

Hi Apologies,

This is for Post Process WorkSheet after that i need to add two columns after Column I (Col No. 9) as Col "J" (Col No. 10) and "K" (Col No. 10).

Now i need to vlookup with the previous day's sheet (Sheet1). where ever the data is in Col No. "A"

(Here Sheet1 contains the same columns as the present sheet which is having an extra columns Col J and K which we need to vlookup now.)

The Code which have taken is like below.
[vba]'After copy the Previous Day's sheet to present workbook as Sheet1
Sub vlookup_pre_sheet1()
Dim lastrow As Long
Dim i As Long, ii As Long

With Worksheets("Original")

.Columns("J:K").Insert

' to get the borders like the previous columns

With Columns("I:I").Select
Selection.Copy
Columns("J:K").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End With


lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To lastrow

If .Cells(i, "B").Value <> "" Then

ii = i + 1
Do

ii = ii + 1

If .Cells(ii, "B").Value <> "Product Code" Then

.Cells(ii, "J").Value = ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[ii],Sheet1!R9C[-9]:R65536C[1],10,0)"
.Cells(ii, "K").Value = ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[ii],Sheet1!R9C[-9]:R65536C[1],11,0)"


End If
Loop Until .Cells(ii + 1, "B").Value = ""

i = ii
End If
Next i

.Columns("A").AutoFit
End With
End Sub
[/vba]

Thank you... in Advance.

Regards,

BVSR

[Bob Phillips]

Your code is all on Original, which is the pre-process worksheet. Is that your problem?

[bvsramesh]

Hi,

No, The sheet name is "Original" even after post process...
1) on the "Original" sheet "PorocessData" Macro will run.

2) on the same workbook a new sheet will added which was the previous day's sheet (the code for that here i was not mentioned) with name as Sheet1

3) vlookup shold do the the current sheet (Original) and Sheet1


I need that whether the above code correct or not which i have wrote for vlookup. Please correct me if i am wrong.

Regards,

BVSR

[bvsramesh]

Hi Team,

Plz correct my code as i am in the middle... once this done i can go further. but i have tried but every time for the above code is giving "False" in the both new columns... plz correct my code.

Regards,

BVSR

[Bob Phillips]

I am not seeing the problem, and cannot waste my time trying to figure out what you mean.

If you want me to help further, post a workbook, with the following
a) a copy of the worksheet before you run any code on it
b) a copy of the worksheet after you run the code on it showing the columns with False in there
c) the current code that you are running.

[bvsramesh]

Hi Apologies for all,

Now i have Attached (test12.zip) the Work Book which has all the 3 modules which for the final result.

I have attached the sequence of the macros to be run in order. and the error results also there in Original (Post Pros - 1st Step) sheet.

and finally i have shown the results as i need in the sheet "Now Required"


Please help me...

Regards

BVSR

[bvsramesh]

Hi All,

Plz reply me on the above. i know you all r busy.. but plz i am in the middle of my project.

Regards,

BVSR

[bvsramesh]

Hi Team,

Is there any way that i can correct my code... evey thing is good but i am struck except on the above... and i have tried all possible ways that i can but still same error result. so plz give me a possible solution. I know i am aksing now and then but i have no other chance except this Forum.

Regards,

BVSR

[bvsramesh]

Hi Team...

Please help me out this....

Regards,

BVSR

[mancubus]

pls don't send PM's...
post your questions to the original thread...
marking thread as solved, obviously, makes members think problems are solved, questions are answered.

that said, i would start with importing the day's data first, then do the rest.

so your step2 is my step1... your step1 is my step2... and i dont have step3

[VBA]
Public Sub Stock_OOP_1()

Dim rng As Range
Dim lastrow As Long, i As Long, ii As Long

With Worksheets("Sheet1") 'created after running Prv_day_file_Copy_as_Sheet1
Set rng = .Range("A2:K" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Worksheets("Original")
.Columns("A").Insert
.Columns("J:K").Insert
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To lastrow
If .Cells(i, "B").Value <> "" Then
ii = i + 1
Do
ii = ii + 1
.Cells(ii, "A").Value = .Cells(i, "B").Value
If .Cells(ii, "B").Value <> "Product Code" Then
.Cells(ii, "A").Value = .Cells(ii, "A").Value & .Cells(ii, "B").Value
.Cells(ii, "J").Value = Application.VLookup(.Cells(ii, "A").Value, rng, 10, 0)
.Cells(ii, "K").Value = Application.VLookup(.Cells(ii, "A").Value, rng, 11, 0)
End If
Loop Until .Cells(ii + 1, "B").Value = ""
i = ii
End If
Next i
.Columns("A").AutoFit
End With
End Sub[/VBA]

[bvsramesh]

Hi,

Apologies for my Mistake ... Thank you very much....

Regards,

BVSR

[mancubus]

Because you have not using any functions like Concatenate kind of function in the code. If you explain me next time i can take this example in my further projects also.

did you read xld's post #4?