New Analyst - New Problems

T

TIREGUY

New Member
Joined
Aug 28, 2019
Messages
5
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. :confused:

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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
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!
 
Upvote 0
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.

Code: 
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
 
Upvote 0
Michael M said:
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.

Code: 
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
​
Click to expand...


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.​
 
Upvote 0
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 !!
 
Upvote 0
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.

Code: 
[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]
 
Upvote 0
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.
 
Upvote 0
You can do something like this to only write your formulas to the last row of data, rather than always 150

Code: 
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
 
Upvote 0
I'm wondering if the LR should be Col "A"

Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
474
Sean15
Sean15
D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
215
MARK858
MARK858
S
  • Solved
VBA autofilling code
Replies
6
Views
603
Peter_SSs
Peter_SSs
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
191
Alex Blakenburg
A
T
  • Question Question
ActiveSheet. Paste error
Replies
1
Views
354
RoryA
RoryA

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top