Delete data Help!!

[Yossy]

can anyone help me include the skip code or make the code work while it
ignore those sheets where it does not find the string (Tile in month for the
Period), All help appreciated.

Sub Clearcontent()
Dim targetcol As String
Dim sh As Worksheet
Dim myrow As Long
Dim lastrowtodelete As Long

targetcol = "A"
For Each sh In ActiveWorkbook.Sheets
'If ActiveSheet.Name <> sh.Name Then

With sh
myrow = .Columns(targetcol).Find(What:="*Tile in month for the Period*", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row + 1
If Application.Trim(Left(.Cells(myrow, 1), 3)) _
= "See" Then myrow = myrow + 1

lastrowtodelete = .Cells(myrow, targetcol).End(xlDown).Row
..Range(.Cells(myrow, targetcol), .Cells(lastrowtodelete,
targetcol)).ClearContents
End With

'End If
Next
End Sub

Thanks a big bunch. I really appreciate it.

 

[OssieMac]

Hi Yossy,

I have now answered you other post. sorry it took so long but I have been at
work. Here it is again. See your other post for explanation.

Sub Clearcontent()
Dim targetcol As String
Dim sh As Worksheet
Dim myrow As Long
Dim lastrowtodelete As Long

Dim objCellToFind As Object

targetcol = "A"
For Each sh In ActiveWorkbook.Sheets
'If ActiveSheet.Name <> sh.Name Then

With sh
Set objCellToFind = .Columns(targetcol). _
Find(What:="*Tile in month for the Period*", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If objCellToFind Is Nothing Then
'*Tile in month for the Period* not found
'so bypass code down to Next.
GoTo myLabel
Else
myrow = objCellToFind.Row + 1
End If

If Left(Trim(.Cells(myrow, 1)), 3) _
= "See" Then myrow = myrow + 1

lastrowtodelete = .Cells(myrow, targetcol).End(xlDown).Row

.Range(.Cells(myrow, targetcol), .Cells(lastrowtodelete, _
targetcol)).ClearContents

End With

'End If

myLabel:
Next
End Sub

 

[Yossy]

Thanks Ossie!!.
Truly appreciate it. Can you help me with this V-Lookup Project.
I need help in vlook up using macro. I have multiple sheets in a Sample
Workbook and would like to lookup Cell E5 from all sheets in the Sample
Workbook from a data range in another Data Workbook and return column two
data.

I want the result to be displayed under the title(Tile in period for the
Month) across the multiple sheets in Sample workbook based on their
corresponding E5 cell lookup value.

Will reallyyyyyy appreciate if you can help me with this. REspectfully
submitted and Thanks for my trouble..

 

[OssieMac]

Hi Yossy,

I was not sure whether you wanted the vlookup in the next cell under the
title or if there is other data under the title and you wanted it in the
first blank cell so I found the first blank cell.

Also I changed the find to remove the wild cards. Changing xlWhole to xlPart
removes the need for the wild cards at beginning and end of a string. You
only need the wild cards if they are used in lieu of middle characters like
this.
"Tile in*the Period"

If you check out the cell where the vlookup formula is inserted then you
will be able to see what the concatenation of the string variables is doing
where the code creates the vlookup formula.

I have inserted lots of comments so hope it does what you want but feel free
to get back to me if you need more help.

By the way this looks like it might be a student assignment. Is my
assumption correct?


Sub Do_Vlookup()

Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'Vlookup range workbook
Dim strWbData As String 'Name of workbook with Vlookup range
Dim strShtData As String 'Name of worksheet with Vlookup range
Dim sh As Worksheet 'Each worksheet
Dim objCellToFind As Object 'Cell to find value in
Dim targetCol As String 'Column for find
Dim targetCell As Range 'Cell for vlookup formula

'Assign this workbook to a variable
Set wbThis = ThisWorkbook

'Attempt to assign data workbook to a variable
'will return error if not open and hense the
'On Error routine
On Error Resume Next
'Edit to your data workbook name
Set wbData = Workbooks("Data for Yossy.xlsm")
On Error GoTo 0

'If data workbook is not already open then
'open the data workbook and assign it to a variable
If wbData Is Nothing Then
'Replace path and workbook name with your path and data workbook name
Set wbData = Workbooks.Open _
("C:\Users\Peter\Documents\Excel\Test Macros\Data for Yossy.xlsm")
End If

'Assign data workbook name to string variable
'that is enclosed in square brackets for use in
'Vlookup formula
strWbData = "[" & wbData.Name & "]"

'Assign data sheet name to a string variable
'that is enclosed in square brackets for Vlookup formula
'Edit to your data worksheet name
strShtData = "Sheet1"

'I have assumed that the title is still in
'column A as per the previous code I helped you with.
targetCol = "A"
For Each sh In wbThis.Sheets
With sh
Set objCellToFind = .Columns(targetCol). _
Find(What:="Tile in month for the Period", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If objCellToFind Is Nothing Then
'*Tile in month for the Period* not found
'so bypass code down to Next.
GoTo byPassLabel
Else
'Find first blank cell under title
Set targetCell = objCellToFind
Do
Set targetCell = targetCell.Offset(1, 0)
Loop Until targetCell = ""
End If

'Insert Vlookup formula in cell.
'Edit the range to suit your range
targetCell = "=VLOOKUP(E5,'" & strWbData & strShtData & _
"'!$A$2:$B$27,2,FALSE)"

End With

byPassLabel:
Next
End Sub

 

[OssieMac]

I forgot to tell you that .xlsm files are xl2007 macro files. Yours will be
xls if not using xl2007.

 

[Yossy]

THankssssssssssssssssss OssieMac. It Works great. Can you advise me on ways
to improve macro skills. Any/all recommendation would be totally appreciated.
I have little knowledge but not indept. What do I need to know and better
grasp to be very good in writing Macro.

Thanks so much!

Hi Yossy,

I was not sure whether you wanted the vlookup in the next cell under the
title or if there is other data under the title and you wanted it in the
first blank cell so I found the first blank cell.

Also I changed the find to remove the wild cards. Changing xlWhole to xlPart
removes the need for the wild cards at beginning and end of a string. You
only need the wild cards if they are used in lieu of middle characters like
this.
"Tile in*the Period"

If you check out the cell where the vlookup formula is inserted then you
will be able to see what the concatenation of the string variables is doing
where the code creates the vlookup formula.

I have inserted lots of comments so hope it does what you want but feel free
to get back to me if you need more help.

By the way this looks like it might be a student assignment. Is my
assumption correct?


Sub Do_Vlookup()

Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'Vlookup range workbook
Dim strWbData As String 'Name of workbook with Vlookup range
Dim strShtData As String 'Name of worksheet with Vlookup range
Dim sh As Worksheet 'Each worksheet
Dim objCellToFind As Object 'Cell to find value in
Dim targetCol As String 'Column for find
Dim targetCell As Range 'Cell for vlookup formula

'Assign this workbook to a variable
Set wbThis = ThisWorkbook

'Attempt to assign data workbook to a variable
'will return error if not open and hense the
'On Error routine
On Error Resume Next
'Edit to your data workbook name
Set wbData = Workbooks("Data for Yossy.xlsm")
On Error GoTo 0

'If data workbook is not already open then
'open the data workbook and assign it to a variable
If wbData Is Nothing Then
'Replace path and workbook name with your path and data workbook name
Set wbData = Workbooks.Open _
("C:\Users\Peter\Documents\Excel\Test Macros\Data for Yossy.xlsm")
End If

'Assign data workbook name to string variable
'that is enclosed in square brackets for use in
'Vlookup formula
strWbData = "[" & wbData.Name & "]"

'Assign data sheet name to a string variable
'that is enclosed in square brackets for Vlookup formula
'Edit to your data worksheet name
strShtData = "Sheet1"

'I have assumed that the title is still in
'column A as per the previous code I helped you with.
targetCol = "A"
For Each sh In wbThis.Sheets
With sh
Set objCellToFind = .Columns(targetCol). _
Find(What:="Tile in month for the Period", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If objCellToFind Is Nothing Then
'*Tile in month for the Period* not found
'so bypass code down to Next.
GoTo byPassLabel
Else
'Find first blank cell under title
Set targetCell = objCellToFind
Do
Set targetCell = targetCell.Offset(1, 0)
Loop Until targetCell = ""
End If

'Insert Vlookup formula in cell.
'Edit the range to suit your range
targetCell = "=VLOOKUP(E5,'" & strWbData & strShtData & _
"'!$A$2:$B$27,2,FALSE)"

End With

byPassLabel:
Next
End Sub

 

[OssieMac]

Hi Yossy,

I'm happy for you that my code helped. On you question of learning, there
are lots of books available. I am lucky where I live because we have free
libraries and they have a good selection of books on Excel. Try for one that
specifically says it is for Excel VBA Programming. One that I particularly
like is VBA and Macros for Microsoft Excel by Bill Jelen.

Once you improve your knowledge a little then spend some time on the forum
and try to solve other peoples problems. doesn't matter if you don't post the
solutions, just try to solve them and then look at what other people have
replied.
--
Regards,

OssieMac

THankssssssssssssssssss OssieMac. It Works great. Can you advise me on ways
to improve macro skills. Any/all recommendation would be totally appreciated.
I have little knowledge but not indept. What do I need to know and better
grasp to be very good in writing Macro.

Thanks so much!

Hi Yossy,

I was not sure whether you wanted the vlookup in the next cell under the
title or if there is other data under the title and you wanted it in the
first blank cell so I found the first blank cell.

Also I changed the find to remove the wild cards. Changing xlWhole to xlPart
removes the need for the wild cards at beginning and end of a string. You
only need the wild cards if they are used in lieu of middle characters like
this.
"Tile in*the Period"

If you check out the cell where the vlookup formula is inserted then you
will be able to see what the concatenation of the string variables is doing
where the code creates the vlookup formula.

I have inserted lots of comments so hope it does what you want but feel free
to get back to me if you need more help.

By the way this looks like it might be a student assignment. Is my
assumption correct?


Sub Do_Vlookup()

Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'Vlookup range workbook
Dim strWbData As String 'Name of workbook with Vlookup range
Dim strShtData As String 'Name of worksheet with Vlookup range
Dim sh As Worksheet 'Each worksheet
Dim objCellToFind As Object 'Cell to find value in
Dim targetCol As String 'Column for find
Dim targetCell As Range 'Cell for vlookup formula

'Assign this workbook to a variable
Set wbThis = ThisWorkbook

'Attempt to assign data workbook to a variable
'will return error if not open and hense the
'On Error routine
On Error Resume Next
'Edit to your data workbook name
Set wbData = Workbooks("Data for Yossy.xlsm")
On Error GoTo 0

'If data workbook is not already open then
'open the data workbook and assign it to a variable
If wbData Is Nothing Then
'Replace path and workbook name with your path and data workbook name
Set wbData = Workbooks.Open _
("C:\Users\Peter\Documents\Excel\Test Macros\Data for Yossy.xlsm")
End If

'Assign data workbook name to string variable
'that is enclosed in square brackets for use in
'Vlookup formula
strWbData = "[" & wbData.Name & "]"

'Assign data sheet name to a string variable
'that is enclosed in square brackets for Vlookup formula
'Edit to your data worksheet name
strShtData = "Sheet1"

'I have assumed that the title is still in
'column A as per the previous code I helped you with.
targetCol = "A"
For Each sh In wbThis.Sheets
With sh
Set objCellToFind = .Columns(targetCol). _
Find(What:="Tile in month for the Period", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If objCellToFind Is Nothing Then
'*Tile in month for the Period* not found
'so bypass code down to Next.
GoTo byPassLabel
Else
'Find first blank cell under title
Set targetCell = objCellToFind
Do
Set targetCell = targetCell.Offset(1, 0)
Loop Until targetCell = ""
End If

'Insert Vlookup formula in cell.
'Edit the range to suit your range
targetCell = "=VLOOKUP(E5,'" & strWbData & strShtData & _
"'!$A$2:$B$27,2,FALSE)"

End With

byPassLabel:
Next
End Sub

 

[Yossy]

Thankssssssssssssssssssssss

Hi Yossy,

I'm happy for you that my code helped. On you question of learning, there
are lots of books available. I am lucky where I live because we have free
libraries and they have a good selection of books on Excel. Try for one that
specifically says it is for Excel VBA Programming. One that I particularly
like is VBA and Macros for Microsoft Excel by Bill Jelen.

Once you improve your knowledge a little then spend some time on the forum
and try to solve other peoples problems. doesn't matter if you don't post the
solutions, just try to solve them and then look at what other people have
replied.
--
Regards,

OssieMac

THankssssssssssssssssss OssieMac. It Works great. Can you advise me on ways
to improve macro skills. Any/all recommendation would be totally appreciated.
I have little knowledge but not indept. What do I need to know and better
grasp to be very good in writing Macro.

Thanks so much!

Hi Yossy,

I was not sure whether you wanted the vlookup in the next cell under the
title or if there is other data under the title and you wanted it in the
first blank cell so I found the first blank cell.

Also I changed the find to remove the wild cards. Changing xlWhole to xlPart
removes the need for the wild cards at beginning and end of a string. You
only need the wild cards if they are used in lieu of middle characters like
this.
"Tile in*the Period"

If you check out the cell where the vlookup formula is inserted then you
will be able to see what the concatenation of the string variables is doing
where the code creates the vlookup formula.

I have inserted lots of comments so hope it does what you want but feel free
to get back to me if you need more help.

By the way this looks like it might be a student assignment. Is my
assumption correct?


Sub Do_Vlookup()

Dim wbThis As Workbook 'This workbook
Dim wbData As Workbook 'Vlookup range workbook
Dim strWbData As String 'Name of workbook with Vlookup range
Dim strShtData As String 'Name of worksheet with Vlookup range
Dim sh As Worksheet 'Each worksheet
Dim objCellToFind As Object 'Cell to find value in
Dim targetCol As String 'Column for find
Dim targetCell As Range 'Cell for vlookup formula

'Assign this workbook to a variable
Set wbThis = ThisWorkbook

'Attempt to assign data workbook to a variable
'will return error if not open and hense the
'On Error routine
On Error Resume Next
'Edit to your data workbook name
Set wbData = Workbooks("Data for Yossy.xlsm")
On Error GoTo 0

'If data workbook is not already open then
'open the data workbook and assign it to a variable
If wbData Is Nothing Then
'Replace path and workbook name with your path and data workbook name
Set wbData = Workbooks.Open _
("C:\Users\Peter\Documents\Excel\Test Macros\Data for Yossy.xlsm")
End If

'Assign data workbook name to string variable
'that is enclosed in square brackets for use in
'Vlookup formula
strWbData = "[" & wbData.Name & "]"

'Assign data sheet name to a string variable
'that is enclosed in square brackets for Vlookup formula
'Edit to your data worksheet name
strShtData = "Sheet1"

'I have assumed that the title is still in
'column A as per the previous code I helped you with.
targetCol = "A"
For Each sh In wbThis.Sheets
With sh
Set objCellToFind = .Columns(targetCol). _
Find(What:="Tile in month for the Period", _
after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If objCellToFind Is Nothing Then
'*Tile in month for the Period* not found
'so bypass code down to Next.
GoTo byPassLabel
Else
'Find first blank cell under title
Set targetCell = objCellToFind
Do
Set targetCell = targetCell.Offset(1, 0)
Loop Until targetCell = ""
End If

'Insert Vlookup formula in cell.
'Edit the range to suit your range
targetCell = "=VLOOKUP(E5,'" & strWbData & strShtData & _
"'!$A$2:$B$27,2,FALSE)"

End With

byPassLabel:
Next
End Sub