Posts by comboboxhelppls

Quote from KjBox

A

My code took 15 minutes and your code takes less than 5 seconds, see why I'm perplexed? I couldn't even reply before, I'm just zoning in my mind how good that is, because now I can fix the input/output of iterations and transactions... but I need to understand your code better to be able to do that, it would be similar but I'm not a seasoned coder

Quote from KjBox

Try this.

Code

Sub GetBusExpenses()
Dim x, y(), z, i As Long, ii As Long, lCnt As Long


x = Page1.Cells(1).CurrentRegion
For i = 2 To Page1.[q2]
If x(i, 8) <> "" Then
lCnt = lCnt + 1: ReDim Preserve y(1 To 11, 1 To lCnt)
y(1, lCnt) = x(i, 8): y(2, lCnt) = x(i, 1): y(3, lCnt) = x(i, 2): y(4, lCnt) = x(i, 4)
y(5, lCnt) = x(i, 5): y(6, lCnt) = x(i, 3): y(7, lCnt) = x(i, 9): y(8, lCnt) = x(i, 10)
y(9, lCnt) = x(i, 7): y(10, lCnt) = x(i, 13): y(11, lCnt) = x(i, 14)
End If
Next
ReDim z(1 To lCnt, 1 To 11)
For i = 1 To lCnt
For ii = 1 To 11
z(i, ii) = y(ii, i)
Next
Next
With Page2
.[a4].Resize(300000, 11).ClearContents
.[a4].Resize(lCnt, 11) = z
End With


End Sub

Display More

I know my request is ambitious but i'm perplexed by your code. I thoroughly believe that a human who helps another with beautiful information like this deserves a better future.

What's the logic behind these three blocks: .
1) [a4].Resize(lCnt, 11) = z
2) lCnt = lCnt + 1: ReDim Preserve y(1 To 11, 1 To lCnt)
3) ReDim z(1 To lCnt, 1 To 11)
For i = 1 To lCnt
For ii = 1 To 11
z(i, ii) = y(ii, i)

ps it worked! im amazed, perplexed, everyone who helped is k'eng and IDK MY WEEKEND HAS BEEN MADE, I DIDNT KNOW I WOULD FIND SOMETHING AS BEAUTIFUL AS WHAT I LEARNED WITH YOU

This macro is for a bus workshop, it reviews the excel DB and makes me a report of all the expenses per bus which ends up giving 2k rows.

Can you please tell me how to speed up this macro? 15 minutes is a lot of time.

||| macro below:

[I]Sub getallExpensesperbus()[/I]


Application.ScreenUpdating = False


Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim selectsheet As Worksheet


Dim idcamion As String
Dim finalrow As Integer
Dim i As Integer


Set datasheet = Page1
Set reportsheet = Page2
Set selectsheet = Page3


reportsheet.Select
reportsheet.Range("A4:K300000").ClearContents
Sheets("BDProducts").Visible = True
datasheet.Select
finalrow = Page1.Range("Q2").Value


For i = 2 To finalrow
If Cells(i, 8).Value <> "" Then
price = Cells(i, 3).Value
ID = Cells(i, 2).Value
product = Cells(i, 1).Value
unit = Cells(i, 4).Value
quantity = Cells(i, 5).Value
address = Cells(i, 9).Value
time = Cells(i, 10).Value
comments = Cells(i, 7).Value
side = Cells(i, 13).Value
mileage = Cells(i, 14).Value
bus= Cells(i, 8).Value


reportsheet.Select
finalrow2 = WorksheetFunction.CountA(Range("A:A")) + 2


Cells(finalrow2, 1).Value = bus
Cells(finalrow2, 2).Value = product
Cells(finalrow2, 3).Value = ID
Cells(finalrow2, 4).Value = unit
Cells(finalrow2, 5).Value = quantity
Cells(finalrow2, 6).Value = price


Cells(finalrow2, 7).Value = adress
Cells(finalrow2, 8).Value = time
Cells(finalrow2, 9).Value = comments
Cells(finalrow2, 10).Value = side
Cells(finalrow2, 11).Value = mileage


datasheet.Select
End If
Next i


reportsheet.Select
Range("A4").Select


Application.ScreenUpdating = True


[I]End Sub[/I]

Hi!
How are you?

I really need your help to resolve 1 simple question that will enable me to finish my work.

Question: How can I clear an activex combobox selection with my macros through a VBA sub if i'm not using a form? the activex combobox is in a sheet, without a form.
Basically I have a macro that reads an activex combobox and does something, when it finishes it's job I need the A.C. to go from [ATTACH=CONFIG]73495[/ATTACH] to [ATTACH=CONFIG]73496[/ATTACH], that's it

Background info:
I created 3 buttons to edit, add or delete entries from a list in another sheet acting as a database, the entry to be modified is selected by the user from an activex combobox dropdown list.
The problem is that after the buttons' macro finishes their job, I need the entry selected to not be selected anymore in the activex combobox for 'cleanliness purposes' and to avoid 'layer 8 mistakes', currently the user selection remains there.