New Analyst - New Problems

[TIREGUY]

Hello all, I am a recent college grad (<12mo) and recently began a new career as an analyst. I am working on a simple macro to change and move data into a uniform format and have run into a small snag. I am attempting to get the macro to completely skip blank cells (and preferably delete the empty blank cells from the list) as the function is, as of now, inputting a standard answer which makes every data set filled with generic duplicates.

I can input any number of initial data points between cells B2:B150 with any number of blanks mixed in and I will inevitably end up with all 150 cells of the range filled with data. I am trying to better understand and utilize VBA so I tried applying an IF to the function in question to return a "" upon <>"" but I continue to receive syntax errors and I feel like I'm barking up the wrong thought-tree.

Any help from the much more experienced and wiser folks around here?

Code in question:

Sub Pickup_Time()
'
' Pickup_Time Macro
'
'
Range("P2").Select
ActiveCell.FormulaR1C1 = "=ROUNDDOWN(RC[-1],-2)/2400+MOD(RC[-1],100)/1440"
Range("B2:B150").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-15
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("N2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TODAY()+3"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N150"), Type:=xlFillDefault
Range("N2:N150").Select
ActiveWindow.SmallScroll Down:=-156
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P150"), Type:=xlFillDefault
Range("P2:P150").Select
Range("P2:P150").Select
Range("P3").Activate
ActiveWindow.SmallScroll Down:=-36
Range("Q2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]+RC[-1]"
Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q150"), Type:=xlFillDefault
Range("Q2:Q150").Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yy h:mm;@"
Range("N2:R150").Select
Selection.ClearContents
End Sub

 

[jorismoerings]

Hi,

This is the first response to your question on MrExcel.

In many cases we're able to help but to do so we require information to go on and we live by a set of rules and use guidelines we all use on MrExcel.
This is not to make your life difficult but just to make sure if somebody responds to your question, he/she gets all the information nescesarry to actually be able to help you!

Unfortunately your post didn't provide the information needed to help or solve your issue.
So instead of a answer i've got a request. Can you take a look at the Forum Rules and also useful to look at Forum Guidelines and create an update of your question in a next post. By doing that you'll help us to help you!

 

[Michael M]

I agree with @jorismoerings....I don't understand what you are trying to do.
But I have shortened the code which may help to help you give us more information.

Sub Pickup_Time()

Range("P2:P150").Formula = "=ROUNDDOWN(O2,-2)/2400+MOD(O2,100)/1440"
Range("B2:B150").Copy
Range("O2").PasteSpecial Paste:=xlPasteValues
Range("N2:N150").Formula = "=TODAY()+3"
Range("Q2:Q150").Formula = "=N2+P2"
Range("Q2:Q150").Copy
Range("B2:B150").PasteSpecial Paste:=xlPasteValues
Range("B2:B150").NumberFormat = "m/d/yy h:mm;@"
Range("N2:R150").ClearContents
End Sub

 

[TIREGUY]

I agree with @

​

jorismoerings

....I don't understand what you are trying to do.
But I have shortened the code which may help to help you give us more information.

Sub Pickup_Time()[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Range("P2:P150").Formula = "=ROUNDDOWN(O2,-2)/2400+MOD(O2,100)/1440"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Range("B2:B150").Copy[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Range("O2").PasteSpecial Paste:=xlPasteValues[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Range("N2:N150").Formula = "=TODAY()+3"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Range("Q2:Q150").Formula = "=N2+P2"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Range("Q2:Q150").Copy[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Range("B2:B150").PasteSpecial Paste:=xlPasteValues[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Range("B2:B150").NumberFormat = "m/d/yy h:mm;@"[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Range("N2:R150").ClearContents[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub

​

Thanks so much to both of you! The shortened code works much faster.. obviously I have MUCH to learn. My apologies.

So... I have 150 available lines for trailer pickup dates and times. Our ERP exports some of these dates / times in a format of MM/DD/YYYY HH:MM (which I originally plan on having them export to line 151 and below) and the other WMS requires them to type the date and time manually into each cell, often 80-130 lines, so it's time consuming as it wont export. I was writing this to shorten the time needed that they would have to take to input the data as the worksheet is ​

always

for the next business day (hence the example above of TODAY()+3 for Friday going to Monday). This allows them to just type the time for pickup in HHMM (ie. 1600) format and let the macro automatically format everything within the B2:B150 range to the same change them to MM/DD/YYYY HH:MM format for filtering purposes.

The problem(s) lies in the blank cells, as some days may require them to use 67/150 lines in the range and others may require 130/150 available. The blank cells will always end with a next day date (or date +3 days) and a time of 00:00 due to the date/time serial number -- and, the blank cells between the final cell of data (ex. B67) and the beginning of the exported lines (B151) would ideally be deleted out at the end of the function so there is no gap between data sets. I tried using an IF function on the ROUNDDOWN/MOD function to ignore the blank cells but I still get the same end result with them having a default date and time inserted. I frankly do not know how to do the coding to find and delete the blank rows in the variable space between one set of data and the next. I hope this clarifies my problem a little better.​

 

[Michael M]

I guessing you want to remove blanks rows and continue the data set from the last used row ??
So, you don't really need the formulas to go the whole 150 rows, only as far as the data goes down each time. which as you say, may be 67 rows, or 130 rows OR 150 rows. Is that correct ?
And finally, which column is the data imported into from ERP (whatever ERP is).....remember, we know nothing about what you are doing, nor can we see the layout of your data. You may think you need.....but you may not !!

 

[TIREGUY]

Exactly! I only set the function to cover the 150 rows because I can't define a needed amount ever as each day is different. The macro will insert a date/time into the blank cells as of now, so I've been trying to determine a way for the macro to skip over blank cells and then delete them at the end.

ERP is Enterprise Resource Planning, essentially a group of integrated products that do different things within a similar system that work cohesively. The best way to describe it is a vastly more complicated suite of products like MS Office, except the products are all integrated together in a single interface.

Anywho.. the ERP will import into B column at the end of the data that's already there, so for now I have it import to Row 151 and just copy/paste it to the end of the data that the macro formats.

[TABLE="width: 1526"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
Load/Pickup Number[/TD]
[TD="align: left"][TABLE]
<tbody>[TR]
[TD]Pickup Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]OE? Y/N[/TD]
[TD]SCAC Code/ Load #[/TD]
[TD]Trailer Number[/TD]
[TD]UOT[/TD]
[TD]# of Stops[/TD]
[TD]Units[/TD]
[TD]Weight (LBS)[/TD]
[TD]Destination City & State[/TD]
[TD]Time Carrier Notified to Pickup[/TD]
[TD]Verifier Initials[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]9/2/19 0:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]9/2/19 0:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]9/2/19 0:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]9/2/19 0:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]9/2/19 0:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]9/2/19 0:00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[TIREGUY]

I posted some sample data and 'Pickup Time' is my Column B. That repeated 9/2/19 0:00 is the output for today that the macro gives when the cell is blank. Tomorrow would be 9/3 and then Sunday would be 9/4 and so on with a time of 0:00. I could not get an IF function to work in order to avoid that output.

 

[JumboCactuar]

You can do something like this to only write your formulas to the last row of data, rather than always 150

Sub Pickup_Time()


'Get the Last row of Column A
LR = Range("A65000").End(xlUp).Row


Range("P2:P:" & LR).Formula = "=ROUNDDOWN(O2,-2)/2400+MOD(O2,100)/1440"
Range("B2:B:" & LR).Copy
Range("O2").PasteSpecial Paste:=xlPasteValues
Range("N2:N:" & LR).Formula = "=TODAY()+3"
Range("Q2:Q:" & LR).Formula = "=N2+P2"
Range("Q2:Q:" & LR).Copy
Range("B2:B:" & LR).PasteSpecial Paste:=xlPasteValues
Range("B2:B:" & LR).NumberFormat = "m/d/yy h:mm;@"
Range("N2:R:" & LR).ClearContents


End Sub

i dont know how your data looks but change the A in LR = Range("A65000").End(xlUp).Row to what ever row of data is fixed

 

[Michael M]

I'm wondering if the LR should be Col "A"

Sub Pickup_Time()
Dim Lr as long
'Get the Last row of Column B
LR = cells(Rows.count,"B").end(xlup).row
Range("P2:P:" & LR).Formula = "=ROUNDDOWN(O2,-2)/2400+MOD(O2,100)/1440"
Range("B2:B:" & LR).Copy
Range("O2").PasteSpecial Paste:=xlPasteValues
Range("N2:N:" & LR).Formula = "=TODAY()+3"
Range("Q2:Q:" & LR).Formula = "=N2+P2"
Range("Q2:Q:" & LR).Copy
Range("B2:B:" & LR).PasteSpecial Paste:=xlPasteValues
Range("B2:B:" & LR).NumberFormat = "m/d/yy h:mm;@"
Range("N2:R:" & LR).ClearContents
End Sub