	AllExperts > VB Script
	Search

Volunteer
Answers to thousands of questions

Home · More VB Script Questions · Answer Library

· Encyclopedia ·

VB Script - How to loop for each worksheet?

Expert: David Barrett - 7/24/2008

Question
Hi,
Step1:
I have a script that searches for a particular data from worksheet one, and pastes all the matched records in worksheet 2.
Step2:
Similarly i want to repeat the search for another data from worksheet one and paste all the matched records in worksheet 3.
...
and so on i want to continue.

One option would be to write sub routines for each step. However i am seeing inconsistency in the behavior. So i want to now write one subroutine that takes parameters like data to be searched, From Worksheet , To worksheet.

Then the subroutine should match the data from the "From Worksheet" and paste the record in "To worksheet"

already existing subroutine is as below.

Sub CreatLink_For_All(PR_Type)
intRow = 3
intRow_For_Sheet = 3
slno = 0
strLink = "http://gnatsweb.broadcom.com/cgi-bin/gnatsweb.pl?debug=&database=DigitalVideo&cmd=view+audit-trail&cmd=view&pr="
strCell = "B" + Trim(Str(intRow))
strCell_BLR_SQA = "B" + Trim(Str(intRow_For_Sheet))
While Sheet2.Range(strCell) <> ""
   If Sheet2.Range(strCell) = "PR_Type" Then
       slno_BLR_SQA = slno_BLR_SQA + 1
       Sheet3.Cells(intRow_For_Sheet, 1) = slno_BLR_SQA
       Sheet3.Cells(intRow_For_Sheet, 2) = Sheet2.Cells(intRow, 2)
       Sheet3.Cells(intRow_For_Sheet, 3) = Sheet2.Cells(intRow, 3)

       strLink_BLR_SQA = strLink & Sheet3.Cells(intRow_For_Sheet, 3)
       Sheet3.Hyperlinks.Add Anchor:=Cells(intRow_For_Sheet, "C"), _
       Address:=strLink_BLR_SQA, TextToDisplay:=""

       Sheet3.Cells(intRow_For_Sheet, 4) = Sheet2.Cells(intRow, 4)
       Sheet3.Cells(intRow_For_Sheet, 5) = Sheet2.Cells(intRow, 5)
       Sheet3.Cells(intRow_For_Sheet, 6) = Sheet2.Cells(intRow, 6)
       Sheet3.Cells(intRow_For_Sheet, 7) = Sheet2.Cells(intRow, 7)
       Sheet3.Cells(intRow_For_Sheet, 8) = Sheet2.Cells(intRow, 8)
       Sheet3.Cells(intRow_For_Sheet, 9) = Sheet2.Cells(intRow, 9)

       intRow_For_Sheet = intRow_For_Sheet + 1
       'strCell_BLR_SQA = "B" + Trim(Str(intRow_For_Sheet))
   End If


   'Sheet2.Activate
   intRow = intRow + 1
   strCell = "B" + Trim(Str(intRow))


'Change "A" to the column with the filenames
Wend

PLEASE HELP ME ..
THANK YOU>

Answer
I am not sure entirely what you mean, but if you mean you want to use the above sub to work on different source/target sheets and search for data as specified by a parameter, then the below code will do this.

Simply call the sub like this:

CreatLink_For_All "PR_DATA", Sheets("Sheet1"), Sheets("Sheet2")

Where "PR_DATA" is the string being searched for, Sheets("Sheet1") is the source sheet, and Sheets("Sheet2") is the target sheet.

Sub CreatLink_For_All(strSearch as string, wsSource as worksheet, wsTarget as worksheet)
intRow = 3
intRow_For_Sheet = 3
slno = 0
strLink = "http://gnatsweb.broadcom.com/cgi-bin/gnatsweb.pl?debug=&database=DigitalVideo&cmd=view+audit-trail&cmd=view&pr="
strCell = "B" + Trim(Str(intRow))
strCell_BLR_SQA = "B" + Trim(Str(intRow_For_Sheet))
While wsSource.Range(strCell) <> ""
  If wsSource.Range(strCell) = strSearch Then
     slno_BLR_SQA = slno_BLR_SQA + 1
     wsTarget.Cells(intRow_For_Sheet, 1) = slno_BLR_SQA
     wsTarget.Cells(intRow_For_Sheet, 2) = wsSource.Cells(intRow, 2)
     wsTarget.Cells(intRow_For_Sheet, 3) = wsSource.Cells(intRow, 3)

     strLink_BLR_SQA = strLink & wsTarget.Cells(intRow_For_Sheet, 3)
     wsTarget.Hyperlinks.Add Anchor:=Cells(intRow_For_Sheet, "C"), _
     Address:=strLink_BLR_SQA, TextToDisplay:=""

     wsTarget.Cells(intRow_For_Sheet, 4) = wsSource.Cells(intRow, 4)
     wsTarget.Cells(intRow_For_Sheet, 5) = wsSource.Cells(intRow, 5)
     wsTarget.Cells(intRow_For_Sheet, 6) = wsSource.Cells(intRow, 6)
     wsTarget.Cells(intRow_For_Sheet, 7) = wsSource.Cells(intRow, 7)
     wsTarget.Cells(intRow_For_Sheet, 8) = wsSource.Cells(intRow, 8)
     wsTarget.Cells(intRow_For_Sheet, 9) = wsSource.Cells(intRow, 9)

     intRow_For_Sheet = intRow_For_Sheet + 1
     'strCell_BLR_SQA = "B" + Trim(Str(intRow_For_Sheet))
  End If
  intRow = intRow + 1
  strCell = "B" + Trim(Str(intRow))
Wend
end sub

Add to this Answer Ask a Question