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 online now Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,230
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
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 02:44 AM.


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