Last x Days Posts  1 | 3 | 7 Days  Search  Topics  Tree View  Help
  Soarer Central * Off-Topic * Any VBA Experts out there? Previous Previous    Next Next  

Author Message
Antony Borlase
TryHard
QLD
UZZ31 (V8 Limited)

Posts: 239
Reg: 07-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Thursday, November 30, 2006 - 01:55 pm, by:  Antony Borlase (Borlase) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

Got a couple of questions regarding VBA, more specifically using a VBA For/IF/Next loop to summarise a excel dump of records to graphing purposes.

Wondering the best way of converting 4 or so columns on a file that is from 5 to 20,000 lines long into a table on a new sheet, and then generating the graphs from this (tried Pivot tables by some values may be present on one project, but not the other 5)
Toan Nguyen
TryHard
VIC
TT

Posts: 145
Reg: 07-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Thursday, November 30, 2006 - 08:43 pm, by:  Toan Nguyen (Soarer_gt) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

Are you after a VB script for excel or are you actually using MS Access in an application and importing the MS Excel sheet for processing.

I dont have MS Access installed on my laptop, so I cant give you 100% code, but here is the logic for what you wanna do.

if the data is in incremental order as how you wish to graph it, select a random number like 50 as your divider.

While loop != eof
sheet2value = sum (start of row : end of row) / divider
pointer move 50+1 paces
end while
Antony Borlase
TryHard
QLD
UZZ31 (V8 Limited)

Posts: 241
Reg: 07-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Thursday, November 30, 2006 - 10:10 pm, by:  Antony Borlase (Borlase) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

It is a Excel or CSV dump from an SQL database.

In short, halfway through doing the third of five 'Make life easier for the project staff' by designed/creating macro's to all all projects to get the same repaetable results on output from our EAD system. The code I have so far (and ignore the big long sections of texty stuff, I've only been doing this for a month)

Sub NCRtest()
'
' Non-Conformance Report Macro
' Macro created 24/11/2006 by aborlase
'
' Setup numbers
Dim Lines As Integer
Dim Status As Integer
Dim TTC As Integer
Dim Cause As Integer
Dim NCR0, NCR1, NCR2 As Variant

' Make everything look hidden and fast
ActiveSheet.Name = "Data"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = "Summarising NCR Export for Report"
ActiveWindow.Zoom = 75

'Get rid of blank sheets (there can only be one!)
Dim sh As Variant
For Each sh In Sheets
If Not IsChart(sh) Then
If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then sh.Delete
End If
Next sh

' First find out how big
FinalRow = Cells(65536, 1).End(xlUp).Row
FinalCol = Cells(1, 255).End(xlToLeft).Column

' Change to "proper" dates and insert summary columns and sort
Sheets("Data").Select
Columns("C:G").NumberFormat = "d/mm/yy;@"
Columns("A:E").Insert Shift:=xlToRight
Columns("A:B").NumberFormat = "0"
Range("A1").FormulaR1C1 = "Code"
Range("A2").FormulaR1C1 = "=RC[13]&RC[2]&RC[18]"
Range("B1").FormulaR1C1 = "=COUNT(R[1]C:R[" & FinalRow & "]C)"
Range("B2").FormulaR1C1 = _
"=IF(AND(RC[9]>0,RC[12]=2),RC[9]-RC[7],TODAY()-RC[7])"
Range("C1").FormulaR1C1 = "TTC"
Range("C2").FormulaR1C1 = _
"=IF(RC[-1]>150,8,IF(AND(RC[-1]<151,rc[-1]>99),7,IF(AND(RC[-1]<101,rc[-1]>74),6,IF(AND(RC[-1]<75,rc[ -1]>49),5,IF(AND(RC[-1]<50,rc[-1]>29),4,IF(AND(RC[-1]<30,rc[-1]>15),3,IF(AND(RC[-1]<16,rc[-1]>5),2,1 )))))))"
Range("D1").FormulaR1C1 = "Cost"
Range("D2").FormulaR1C1 = "=VALUE(RC[17])"
Range("E1").FormulaR1C1 = "Days"
Range("E2").FormulaR1C1 = "=VALUE(RC[19])"
Range("A2").Resize(FinalRow - 1, 5).FillDown
Cells.Sort Key1:=Range("N2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Key3:=Range("S2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("A1").Resize(FinalRow, FinalCol + 5).Name = "ncr"

' Insert Summary Worksheet and names
Worksheets.Add After:=Worksheets(1)
ActiveSheet.Name = "Summary"
ActiveWindow.Zoom = 75
Range("A2").FormulaR1C1 = "Cause"
Range("A3").FormulaR1C1 = "0"
Range("A4").FormulaR1C1 = "1"
Range("A5").FormulaR1C1 = "2"
Range("A6").FormulaR1C1 = "3"
Range("A7").FormulaR1C1 = "4"
Range("A8").FormulaR1C1 = "5"
Range("A9").FormulaR1C1 = "6"
Range("A10").FormulaR1C1 = "7"
Range("A11").FormulaR1C1 = "8"
Range("A12").FormulaR1C1 = "9"
Range("A13").FormulaR1C1 = "10"
Range("A14").FormulaR1C1 = "11"
Range("A15").FormulaR1C1 = "12"
Range("A16").FormulaR1C1 = "13"
Range("A17").FormulaR1C1 = "14"
Range("B2").FormulaR1C1 = "Name"
Range("B3").FormulaR1C1 = "Health & Safety"
Range("").FormulaR1C1 = "Design - Incorrect Info supplied"
Range("B5").FormulaR1C1 = "Supply - Incorrect Info supplied"
Range("B6").FormulaR1C1 = "Construct - Incorrect Info Supplied"
Range("B7").FormulaR1C1 = "Spare"
Range("B8").FormulaR1C1 = "Spare"
Range("B9").FormulaR1C1 = "Environmental Hazard"
Range("B10").FormulaR1C1 = "Design - DWG incorrect"
Range("B11").FormulaR1C1 = "Design - DOC/SPEC incorrect"
Range("B12").FormulaR1C1 = "Supply - Not to DWG"
Range("B13").FormulaR1C1 = "Supply - Not to DOC/SPEC"
Range("B14").FormulaR1C1 = "Supply - Transport/Packaging"
Range("B15").FormulaR1C1 = "Construct - Not to DWG"
Range("B16").FormulaR1C1 = "Construct - Not to DOC/SPEC"
Range("B17").FormulaR1C1 = "Construct - Storage/Handling"
Range("C2").FormulaR1C1 = "TCC1"
Range("D2").FormulaR1C1 = "TCC2"
Range("E2").FormulaR1C1 = "TCC3"
Range("F2").FormulaR1C1 = "TTC4"
Range("G2").FormulaR1C1 = "TTC5"
Range("H2").FormulaR1C1 = "TTC6"
Range("I2").FormulaR1C1 = "TTC7"
Range("J2").FormulaR1C1 = "TTC8"
Range("C1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Open"
Range("K2").FormulaR1C1 = "TCC1"
Range("L2").FormulaR1C1 = "TCC2"
Range("M2").FormulaR1C1 = "TCC3"
Range("N2").FormulaR1C1 = "TTC4"
Range("O2").FormulaR1C1 = "TTC5"
Range("P2").FormulaR1C1 = "TTC6"
Range("Q2").FormulaR1C1 = "TTC7"
Range("R2").FormulaR1C1 = "TTC8"
Range("K1:R1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Outstanding"
Range("s2").FormulaR1C1 = "TCC1"
Range("t2").FormulaR1C1 = "TCC2"
Range("u2").FormulaR1C1 = "TCC3"
Range("v2").FormulaR1C1 = "TTC4"
Range("w2").FormulaR1C1 = "TTC5"
Range("x2").FormulaR1C1 = "TTC6"
Range("y2").FormulaR1C1 = "TTC7"
Range("z2").FormulaR1C1 = "TTC8"
Range("S1:Z1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "Closed"

' We now have the Summary Worksheet so lets names the ranges
NCR0 = Worksheets("Summary").Range("C3:J14")
NCR0 = Worksheets("Summary").Range("K3:R14")
NCR0 = Worksheets("Summary").Range("S3:Z14")

' Now do For...Next...If loop for each line/Status/TTC/Cause
Worksheets("Data").Select
For Lines = 2 To FinalRow
For Status = 0 To 2
For TTC = 1 To 8
For Cause = 0 To 14
Check = Status & TTC & Cause
If Cells(Lines, 1).Value = Check Then
If Cells(Lines, 14) = 0 Then NCR0(Cause, TTC) = NCR0(Cause, TTC) + 1
ElseIf Cells(Lines, 14) = 1 Then NCR1(TTC, Cause) = NCR1(TTC, Cause) + 1
ElseIf Cells(Lines, 14) = 2 Then NCR2(TTC, Cause) = NCR2(TTC, Cause) + 1
' End If
End If
Next Cause
Next TTC
Next Status
Next Lines

' We have all values now lets place them into the summary
Sheets("Summary").Range("C3").Select
For TTC = 1 To 8
For Cause = 0 To 14
For Status = 0 To 2


Next Status
Next Cause
Next TTC

' Sheets("Summary").Range("C3").FormulaR1C1 = NCRs(0, 1, 1)


' Make everything look normal again and show problems if there
Exits:
Sheets("Summary").Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.StatusBar = False

End Sub

Public Function IsChart(sh) As Boolean
Dim tmpChart As Chart
On Error Resume Next
Set tmpChart = Charts(sh.Name)
IsChart = IIf(tmpChart Is Nothing, False, True)
End Function

I am pretty sure this extracts the data from across each line, but trying to display the array results is just causing me to flip (more so than I normally am)

Any help gratefully accep}ted
Dan McColl
Goo Roo
Victoria
Bog Standard Active

Posts: 1602
Reg: 07-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Thursday, November 30, 2006 - 11:12 pm, by:  Dan McColl (Hoon) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

Holy Crap. Way too much Jibber Jabber.
Benny Gammelmark
Goo Roo
NSW
V8 UZZ31

Posts: 1744
Reg: 07-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Friday, December 01, 2006 - 01:19 am, by:  Benny Gammelmark (Oldfield) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

Read the bloody data into an access database with a field for each coloumn and export the stuff to Excel.
In that way you can do whatever you like with the data.

It will mean that you will have to do a bit of "manual" work the first time but it'll be the quickest way.

Then when everything works that way, make a script that does the export for you.

NB! You should NEVER code variable or semi-variable headings. They should reside in a table.

The code above looks very much like the type of code I would have used when I started 25 years ago (although a bit more simplistic since we didn't have Excel).

And btw: I HATE MICROSOFT PROGRAMMING. It's the only company that ever got away with a flaky language translator.
Antony Borlase
TryHard
QLD
UZZ31 (V8 Limited)

Posts: 243
Reg: 07-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Friday, December 01, 2006 - 09:12 am, by:  Antony Borlase (Borlase) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

Unfortunately these macro's, which I shall be converting in VB Express to standalone apps, are to be circulated to 7 project teams, when means about 5-15 people each project depending on size.

So the manual work you mention would have to be done by 40 or so people, at different times for different reasons on the same data (not all the sites we work on have any communications except for our little satellite dishes)


Benny Gammelmark wrote on Friday, December 01, 2006 - 01:19 am:

Read the bloody data into an access database



Why? we already have a sweet XLS version with the right data in columns.

Maybe I am over-complicating things, but Auto sum/Pivot tables don't work for the graphical results as some projects may not have 'Commissioning' issues yet, and MS doesn't handle NULL values well.

I will have to find a easy way of using these variables in the (so-called) advanced filter, and look at transposing those figures.

The biggest laugh I had is that its bloody similar to Basic that I first used on a Commodore 4016 (way before the VIC 20 everyone), only now you have lovely colours...oh be still my beating heart!

The other thing about this whole macro, is once it works on one DB dump, the applications to run it can have a couple more selection boxes to then allow it to run all the basic project reports. I have finished the ones that do the forecasting on our 5 projects, and also one that does productivity and loss with the costings dumps. Just trying to make many projects all have the same reporting/analysis methods.
Toan Nguyen
TryHard
VIC
TT

Posts: 149
Reg: 07-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Friday, December 01, 2006 - 11:10 am, by:  Toan Nguyen (Soarer_gt) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

You're coding like a scripter instead of a programmer.

to avoid nulls, make a function called "avoid nulls"

= if (somevaluehere = "" , "0" , somevaluehere)
Benny Gammelmark
Goo Roo
NSW
V8 UZZ31

Posts: 1746
Reg: 07-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Friday, December 01, 2006 - 01:13 pm, by:  Benny Gammelmark (Oldfield) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

I still think it would be easier to load into an Access database (or use it directly as a linked table) and do the work there.
Export can be automated but if it's a report you need it wouldn't be that hard to write it in Access.

The nulls issue could also just be done with an update query.

But then I'm a programmer, not an Excel wizard.
Callum Finch
Goo Roo
WA
Soarer TT & Corolla

Posts: 2255
Reg: 09-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Sunday, December 03, 2006 - 03:11 am, by:  Callum Finch (Sigeneat) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

It scares me that ALL OF THIS THREAD makes sense too me.. =S
Toan Nguyen
TryHard
VIC
TT

Posts: 150
Reg: 07-2005

Top of pagePrevious messageNext messageBottom of page Link to this message

Rating: N/A
Votes: 0 (Vote!)

Monday, December 04, 2006 - 01:51 am, by:  Toan Nguyen (Soarer_gt) Quote hilighted text Edit Post Delete Post Print Post   View Post/Check IP (Moderator/Admin Only) Move Post (Moderator/Admin Only)

He needs to export the script to VBE and other applications which negates the import/export idea to MS Access.

I'm with you though. For that amount of information, you need a centralised db or at least a transaction db per team which after processing can sync with the main db. Doing it in excel and VBA will just cause more issues down the track, but it seems the methods and processes that are currently in place seem to work.

It's like those transition periods companies go through where the current technology has reached it's limits. eg. Using excel to do payroll for 3 people is fine... Using excel to do payroll for 500 people starts to be painful.

Add Your Message Here
Eye Candy
Click for full size
Bold text Italics Underline Center Text Upload photo from your hard drive Make a List Make a Table Make an Image Thumbnail Create a hyperlink Insert a clipart image Formatting Help
         

Username: Important Posting Information:
If asking a question, have you done a search to see if your question has already been answered?
Be aware that the use of SMS-speak eg "u" instead of "you" etc, will get your post deleted.
Password:
Enable HTML code in message
Automatically activate URLs in message

  Administration Administration      Log Out Log Out Previous Previous      Next Next