PDA

View Full Version : Excel vba jump to specific row in worksheet



Kaniguan1969
07-18-2014, 12:30 AM
Hi,

I have a requirements to find the specific rows where cells/column has a content of string. In my example: I have to find the string name "Jul"
(Present month Name formatted in 3 char) from the range("D90,090"), once i found the word "Jul" i have to Cut and paste again to the same column as values because presently it has a formula. May i ask your assistance on how to find this in vba macro. Please find my macro code for enhancement. thank you in advance. sample below data in screen shot. Thank you in advance.

Btw, Based on my codes. I already find the string name "Jul" but the problem is how could i obtain the whole columns from J91 to J137.

My VBA code

Dim wb As Workbook
Dim ws As Worksheet, ws1 As Worksheet
Dim Lrow As Long
Dim StartDate As String, EndDate As String, Datefmt As String, TargetDate As String
Dim rng As Range, rng1 As Range, rng2 As Range, Startrange As Range, _
Targetrange As Range, MultipleRange As Range
Set wb = ThisWorkbook
Set ws = wb.Sheets("Monthly")
With ws.Range("B1")
Datefmt = Sheets("Monthly").TextBox1.Text
TargetDate = Format(Datefmt, "mmm") ' MonthName 3 char
End With

Set ws = wb.Sheets("DTC Summary") 'Data source
With ws
Lrow = .Range("B90:B137").Row 'Data rows and columns
Set rng = ws.Range("D90:O90") 'Data source range --Jan to Dec
Set rng1 = rng.Find(TargetDate, , xlValues, xlWhole) 'Find the string "Jul"
Debug.Print rng.Address, rng1.Address
Set ws1 = wb.Sheets("TEST") 'Target output
Targetrange.Offset(1, 0).Copy 'copied to the same position as value only
End With


sample data

westconn1
07-18-2014, 03:29 AM
try like

set rng1 = rng1.offset(1).resize(46)
debug.print rng1.address

Kaniguan1969
07-20-2014, 11:28 PM
Solved.