An excel vba challenge

[adolf hamster]

Ok vba guys, i have a challenge for you;

I have a spreadsheet with 2 tabs; "order history" and "calculations"

On order history there's a table object i've named "ordertable"

What i want to do is find the first blank cell in column 4 of this table, and copy/paste values from fixed cells in the calculations sheet to this first blank cell and a cell 3columns to the right of it.

Any clues? I'm struggling with the whole table object thing, can just about get it to work if it wasn't a table by using .end(xlup) to find the last cell but doing so with the table object in place is eluding me.

If possible i'd like a gap between the pasting of the first and second values as there's an automated macro that runs when the first value is entered.

 

[Grrrrr]

I don't full understand what you need but hopefully this will get you started...

If you create a table then Excel automatically assigns 'names' in the name manager to the columns. This means you can just loop through that named range looking for blanks and insert whatever values you need. This method avoids using 'listobjects' in the VBA code (which is the VBA name for a table).

Sub adolf_hamster()

Dim Rng As Range

With Range("[COLOR="Yellow"]ExampleTable[Brown][/COLOR]").SpecialCells(xlCellTypeBlanks)

   .Cells(1, 1).Value = [COLOR="Yellow"]"Whatever you need from the calculations worksheet"[/COLOR]
   .Cells(1, 1).Offset(0, 3).Value = [COLOR="Yellow"]"Whatever you need from the calculations worksheet"[/COLOR]

End With

End Sub

 

[adolf hamster]

I don't full understand what you need but hopefully this will get you started...

If you create a table then Excel automatically assigns 'names' in the name manager to the columns. This means you can just loop through that named range looking for blanks and insert whatever values you need. This method avoids using 'listobjects' in the VBA code (which is the VBA name for a table).

Sub adolf_hamster()

Dim Rng As Range

With Range("[COLOR="Yellow"]ExampleTable[Brown][/COLOR]").SpecialCells(xlCellTypeBlanks)

   .Cells(1, 1).Value = [COLOR="Yellow"]"Whatever you need from the calculations worksheet"[/COLOR]
   .Cells(1, 1).Offset(0, 3).Value = [COLOR="Yellow"]"Whatever you need from the calculations worksheet"[/COLOR]

End With

End Sub

Looks promising, is there any way to get a delay/cell "update"?

I have another macro that when the " part code" is entered manually it'll autofill a load of values, its a "byval target as range" variety so would this trigger it ok?

 

[Sir SpankalotUK]

Looks promising, is there any way to get a delay/cell "update"?

only way to delay a script is to use

Application.Wait Now + TimeValue("00:00:01")

which delays the script 1 second. Sounds like you want to look at the event handlers, so you have a script that does the initial calcs, then the event handler will trigger another procedure to finish the work off or have a button that the user clicks.

 

[Grrrrr]

I have another macro that when the " part code" is entered manually it'll autofill a load of values, its a "byval target as range" variety so would this trigger it ok?

It should trigger the "byval target as range" macro after this line

.Cells(1, 1).Value = [COLOR="Yellow"]"Whatever you need from the calculations worksheet"[/COLOR]

Then it will resume the rest of the code

I'd just suggest trying and see, otherwise you can just call the macro manually after that line.

To answer the opposite question to what you asked (which you may still find useful). To STOP an automatically triggered macro from running you need to use this code:

Application.EnableEvents = False
[COLOR="Yellow"]<--- code here--->[/COLOR]
Application.EnableEvents = True

 

[adolf hamster]

It should trigger the "byval target as range" macro after this line

.Cells(1, 1).Value = [COLOR="Yellow"]"Whatever you need from the calculations worksheet"[/COLOR]

Then it will resume the rest of the code

I'd just suggest trying and see, otherwise you can just call the macro manually after that line.

To answer the opposite question to what you asked (which you may still find useful). To STOP an automatically triggered macro from running you need to use this code:

Application.EnableEvents = False
[COLOR="Yellow"]<--- code here--->[/COLOR]
Application.EnableEvents = True

That is useful, i might set it to stop the other macro and do that macro's job for it. Basically i didnt want this macro to finish running before the other started (as it'd screw up).

I'll have a wee tinker and see what i come up with, i may be back for some bugfixing

 

[adolf hamster]

Well, managed to get something that works, your code worked great, and did indeed activate the other macro, however i was having issues with the formulas i was using updating then reverting while it was running. So i had to halt the macro's and calculations in the whole workbook and add in the functionality to this macro.

It should be fine, this is for a quick user form with a "push button" entry to a log wheras the other is if they want to manually add things in so the two wont happen at the same time.

Must admit it's a great success to end the day with , i'll post the code up tomorrow for future googlers.

Thanks very much for your help

 

[adolf hamster]

For the googlers of the future, i hope this helps you as so many other random forum posts has helped me:

Sub Copysheet()

'preventing other macros and formulas from updating

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Dim Rng As Range

*

'finding first blank cell in order table part code column and pasting in values

With Range("OrderTable[Part Code]").SpecialCells(xlCellTypeBlanks)

*

.Cells(1, 1).Value = Worksheets("Calculations").Range("C2").Value

Worksheets("Order History").Calculate

.Cells(1, 1).Offset(0, 6).Value = Worksheets("Calculations").Range("H2").Value

.Cells(1, 1).Offset(0, 4).Value = .Cells(1, 1).Offset(, 3).Value

.Cells(1, 1).Offset(0, 9).Value = Format(Now, "dd-mmm-yyyy")

.Cells(1, 1).Offset(0, 10).Value = Format(Now, "hh:mm:ss")

.Cells(1, 1).Offset(0, 8).Value = Application.UserName

.Cells(1, 1).Offset(0, 12).Value = Worksheets("Calculations").Range("L2").Value

End With

*

'Delete Form entry on first page

Worksheets("Overview").Range("D29").ClearContents

Worksheets("Overview").Range("D31").ClearContents

Worksheets("Overview").Range("D33").ClearContents

*

'restoring calculations and other macros functionality and recalculating worksheets

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

Worksheets("Overview").Calculate

Worksheets("Order History").Calculate

Worksheets("Stock Breakdown").Calculate

End Sub