98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » IF and then
IF and then
Resolved · High Priority · Version 2016
Wed 29 Nov 2017, 15:29Delegate Dayu said...
Dayu has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
IF and then
Account Name Pay Date Swap CCY Swap ID Account ID Security Desc ISIN Financing (Swap)
Alternative Eq Compass World 29.11.2017 AUD 308058 1196898 COMMONWEALTH BANK OF AUSTRALIA COM STK AUD AU000000CBA7 285.94
Alternative Eq Compass World 29.11.2017 AUD 308058 1196898 CSL LTD COM STK AUD AU000000CSL8 99.03
Alternative Eq Compass World 29.11.2017 AUD 308058 1196898 NEWCREST MINING LTD COM STK AUD AU000000NCM7 -945.92
Alternative Eq Compass World 29.11.2017 AUD 308058 1196898 SANTOS LTD COM STK AUD AU000000STO6 90.49
Alternative Eq Compass World 29.11.2017 AUD 308058 1196898 TRANSURBAN GROUP COM STK AUD 0 AU000000TCL6 41.91
Alternative Eq Compass World 29.11.2017 AUD 308058 1196898 WESFARMERS LTD COM STK AUD AU000000WES1 161.92
Alternative Eq Compass World 29.11.2017 AUD 308058 1196898 WESTPAC BANKING CORP COM STK AUD AU000000WBC1 -337
Alternative Eq Compass World 29.11.2017 AUD 308058 1196898 Other Cash Balances 0
Alternative Eq Compass World 29.11.2017 AUD 308058 -603.63
Alternative Eq Compass World 29.11.2017 AUD -603.63
i tried to use IF AND to code - if column H have numbers but G columns are blank then delete the row H. my code as below but i doesn't seem working. this code is before i put the loop in. also do i need to loop from the bottom to top. pls advise.
Sub Deleteuplic()
ActiveCell("h10").Select
'For G = 1 To Range("a9").CurrentRegion.Rows.Count - 1
If ActiveCell.Value <> 0 And ActiveCell.Offset(-1, 0).Value = "" Then .EntireRow.Delete
End If
End Sub
Thu 30 Nov 2017, 12:05
Edited on Thu 30 Nov 2017, 12:05Trainer Andreas said...
RE: IF and then
Hi Dayu,
Our VBA trainers are very busy with training today, but one of them will be in the office tomorrow. They will be able to reply to your query then.
Regards,
Andreas
Operation Co-ordinator
Fri 1 Dec 2017, 12:11Trainer Jens said...
RE: IF and then
Hi Dayu,
I am sorry for the late answer.
Please try the code below:
Sub Deleteuplic()
Dim lRows As Long
lRows = Range("a2").CurrentRegion.Rows.Count
For lrow = lRows To 1 Step -1
If Not Cells(lrow, 2).Value Like "#*/11/*#" Then
Cells(lrow, 2).EntireRow.Delete
End If
Next lrow
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 5 Dec 2017, 13:53Trainer Jens said...
RE: IF and then
Hi Dayu,
I am sorry to hear the code is not working.
I cannot understand the code deleted all your records. It should only delete records not in November.
I have just been running the code in the workbook you forwarded to Luke 29 November and it deleted on records not in November.
I was a little to fast replying earlier. I forgot the code where you want to test if column H different from a Empty cell and column G is a Empty cell.
The code below works in the workbook you forwarded to Luke.
Please let me know in the forum if you cannot get it to work.
Sub Deleteuplic()
Dim lRows As Long
lRows = Range("a2").CurrentRegion.Rows.Count
For lrow = lRows To 1 Step -1
If Not Cells(lrow, 2).Value Like "#*/11/*#" Then
Cells(lrow, 2).EntireRow.Delete
End If
If Cells(lrow, 8).Value <> "" And Cells(lrow, 7) = "" Then
Cells(lrow, 2).EntireRow.Delete
End If
Next lrow
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 5 Dec 2017, 15:02Delegate Dayu said...
RE: IF and then
Thanks Jens. it seems deleting all rows when i used two codes. i decided to test the code separately.
this part is working.
If Cells(lrow, 8).Value <> "" And Cells(lrow, 7) = "" Then
Cells(lrow, 2).EntireRow.Delete
End If
but for the other part to remove any data which is not NOV doesn't work.
If Not Cells(lrow, 2).Value Like "#*/11/*#" Then
Cells(lrow, 2).EntireRow.Delete
End If
if i put these two codes together then it deleted all the data on the excel.
Tue 5 Dec 2017, 15:15Delegate Dayu said...
RE: IF and then
also i tried this one (to delete any rows with date 28.12.2017) but doesn't seem working either.
If Cells(lrow, 2).Value = 12 Then Cells(lrow, 2).EntireRow.Delete
Thanks for your help.
Tue 5 Dec 2017, 16:00Trainer Jens said...
RE: IF and then
Hi Dayu,
Try:
If Not Month(Cells(lrow, 2).Value) = 11 Then
Cells(lrow, 2).EntireRow.Delete
End If
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 5 Dec 2017, 16:06Delegate Dayu said...
RE: IF and then
Thanks for your quick response. i tried the code and it comes up with Run-time error "13" type mismatch. should i define the Month?
Dayu
Tue 5 Dec 2017, 16:13Trainer Jens said...
RE: IF and then
Hi Dayu,
I have a feeling, that the file you have is different from the one you sent Luke.
Do you have the date in column B?
If not you must change the code.
Cells(lRow,2) is looking for the date in column 2.
If Not Month(Cells(lrow, 2).Value) = 11 Then
Cells(lrow, 2).EntireRow.Delete
End If
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 5 Dec 2017, 16:18Delegate Dayu said...
RE: IF and then
Hi Jens,
it is the same layout as the one i sent it to Luke and i can see it works but with the debugger error message, which i provided it to you early.
Thanks
Dayu
Tue 5 Dec 2017, 16:26Trainer Jens said...
RE: IF and then
Hi Dayu,
Which Line does Excel highlight when you click debug?
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 5 Dec 2017, 16:29Delegate Dayu said...
RE: IF and then
If Not Month(Cells(lrow, 2).Value) = 11 Then
Tue 5 Dec 2017, 16:39Trainer Jens said...
RE: IF and then
Hi Dayu,
You may have a text string in column B.
If this is the issue you will get the error. You can also try this. Then You will bypass the Debug error.
On Error Resume Next
If Not Cells(lrow, 2).Value Like "#*/11/*#" Then
Cells(lrow, 2).EntireRow.Delete
End If
On Error Goto 0
It is strange because all the codes work fine here.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 5 Dec 2017, 16:34Trainer Jens said...
RE: IF and then
Hi Dayu,
Try to step the code (F8) and see if you get the error first time you get to:
If Not Month(Cells(lrow, 2).Value) = 11 Then
Cells(lrow, 2).EntireRow.Delete
End If
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 5 Dec 2017, 16:40Delegate Dayu said...
RE: IF and then
Hi Jens,
yes, indeed. it shows as an error which i described early.
Tue 5 Dec 2017, 16:46Trainer Jens said...
RE: IF and then
Hi Dayu,
I am running out of ideas.
Can I please ask you to send the file to:
info@stl-training.co.uk
Something strange must be happening in the workbook. I can have a look at it if you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Wed 6 Dec 2017, 10:48Trainer Wendy said...
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Fri 8 Dec 2017, 10:23Trainer Jens said...
RE: IF and then
Hi Dayu,
I have spent some time looking at your data.
The file you sent Luke was not the same as the data you sent me. In the file you sent Luke there are dates in column B. Excel cannot recognise column B in the data you sent me as dates. You have hidden characters in the column and it is text strings not dates. This explain why you got the error 13 Mismatch.
I couldn't get your code (Text to Columns) to work. I have rerecorded the Text to Column and the code works. The code clean the hidden characters, Format to dates and delete the unwanted rows.
Copy the data from the CSV file and paste them in a XLSX workbook and run both code.
Sub ToColumns()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
(20, 1)), TrailingMinusNumbers:=True
End Sub
Sub RemoveRows()
Dim lRows As Long
Dim lRow As Long
lRows = Range("a2").CurrentRegion.Rows.Count
For lRow = lRows To 2 Step -1
Cells(lRow, 2).Value = Application.WorksheetFunction.Clean(Cells(lRow, 2).Value)
Next lRow
For lRow = lRows To 2 Step -1
Cells(lRow, 2).Value = Format(WorksheetFunction.Substitute(Cells(lRow, 2), ".", "/"), "dd/mm/yyyy")
Next lRow
For lRow = lRows To 2 Step -1
If Not Cells(lRow, 2).Value Like "*/12/*" Then
Cells(lRow, 2).EntireRow.Delete
End If
If Cells(lRow, 8).Value <> "" And Cells(lRow, 7) = "" Then
Cells(lRow, 2).EntireRow.Delete
End If
Next lRow
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 8 Dec 2017, 12:14Delegate Dayu said...
RE: IF and then
Thanks for your help Jens. I will test it late.
Thu 14 Dec 2017, 09:29Trainer Wendy said...
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Wed 20 Dec 2017: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Trace Dependents / Precedents without the blue arrowsRather than using the toolbar you can press CTRL+] which is the equivelent of trace dependants and CTRL+[ for precendants. Both of these ways though will not show the blue arrows but jump to the cell containing the formula. |
