Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 08-12-2018, 05:49 AM
Stefaan1973 Stefaan1973 is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2018
Posts: 3
Stefaan1973 is on a distinguished road
Default VBA code ajustment

hi everyone,

i have an vba code that works but i need adjustments

the sheets are made for driver to register their rides.
the vba code sorts the colom "Busjes" (busses) but there are serveral busses not sorted.
we have busje 1 til 6 but we also have mercedes 1 en mercedes 2 this to should be included in the vba macro

can anybody help me please?

here is the code

Sub VenA()
Application.ScreenUpdating = False
ar = Sheets("Voertuig").Cells(1).CurrentRegion
For j = 2 To UBound(ar)
If IsError(Evaluate("'" & ar(j, 1) & "'!A1")) Then Sheets.Add(Sheets(Sheets.Count)).Name = ar(j, 1)
Sheets(ar(j, 1)).Cells.Clear
Sheets(ar(j, 1)).Cells(1).Resize(, 9) = Split("Datum Soort_Rit Traject Busje Chauffeur Tankbeurt Km_Begin Km_Eind Aantal_KM")
Next j

For Each sh In Sheets(Array("Koen", "Stefaan", "Carlos", "Bartje", "Ronny", "Martial", "Emiel", "Bart", "Gilbert", "Sven", "Danny"))
For j = 2 To UBound(ar)
With sh.ListObjects(1).DataBodyRange
tel = WorksheetFunction.CountIf(sh.Range("D"), ar(j, 1))
If tel > 0 Then
.AutoFilter 4, ar(j, 1)
.Offset(0, 0).Resize(.Rows.Count - 1, 9).Copy
Sheets(ar(j, 1)).Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial 12
.AutoFilter
End If
End With
Next j
Next sh

For j = 2 To UBound(ar)


With Sheets(ar(j, 1)).Cells(1).CurrentRegion
.Columns.AutoFit
'andere opmaak dingetjes
.Sort .Cells(1), , , , , , , xlYes

rij = Sheets(ar(j, 1)).Range("A:A").SpecialCells(xlCellTypeVisible).S pecialCells(xlCellTypeConstants).Count + 1
Sheets(ar(j, 1)).Range("i" & rij).Formula = "=SUM(i" & 2 & ":i" & rij - 1 & ")"

End With
Next j
End Sub

Last edited by Stefaan1973; 08-12-2018 at 05:54 AM. Reason: problem solved!!!
Reply With Quote
  #2  
Old 08-12-2018, 07:43 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,163
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Hi and welcome
if your problem is solved click " Thread tools- Mark thread as solved"
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 08-12-2018, 12:31 PM
Stefaan1973 Stefaan1973 is offline Windows 10 Office 2016
Novice
 
Join Date: Aug 2018
Posts: 3
Stefaan1973 is on a distinguished road
Default

problem solved by myself
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
vba code sue68 Word VBA 2 08-31-2017 10:41 PM
Help with Code AJacLo Excel Programming 2 02-19-2017 03:57 PM
VBA Code to search for field codes with certain text before the Field code and to change style welcometocandyland Word VBA 4 02-08-2017 06:53 PM
QR-code qrcode Word 3 05-26-2015 06:38 AM
vbc code rajpeter Excel Programming 2 09-13-2011 02:29 PM


All times are GMT -7. The time now is 01:24 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft