Posts by Bertie

Re: Converting =Now() to YYYYMMDDHHMM

Thanks Will

Sorry, didn't pick up on what you were saying first time round. Have to get people to S p e a k S l o w l y for me :¬>

Re: Converting =Now() to YYYYMMDDHHMM

Hi Norie

Here's the code. Currently it is working fine saving the file as C:\RDEpload.csv. I need to find a way (if possible) to save it as c:\RDEyyyymmddhhmm (the date and time being whatever it is at that instant). Do you / anyone know if this can be done?

Thanks
Bert

[vba]Workbooks.Open Filename:="C:\RDEpload.csv"
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Windows("RDTest2.xls").Activate
Range("A2:L34").Select
Selection.Copy
Windows("RDEpload.csv").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\RDEpload.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
Windows("RDTest2.xls").Activate
Range("A2:B500").Select
Selection.ClearContents
Range("I2:N500").Select
Selection.ClearContents
Range("A2").Select
Application.Quit
ThisWorkbook.Close SaveChanges:=True[/vba]

Re: Converting =Now() to YYYYMMDDHHMM

Had a look and this works great. Thanks.

What I trying to get to ultimately is that I currently have a macro that saves and closes a workbook called 'RDETEST' what I need it to do is save as 'RDE' (always) plus the current yyyymmddhhmm info for that time. Do you know if this can be done??

Thanks

Bert

Re: Converting =Now() to YYYYMMDDHHMM

Thanks People. Will give it a whirl in the morning

Hi

Have put together a small VB script from bits found on the net that I was hopeing would convert =now() to a format of YYYYMMDDHHMM, for now I've attached it to a button, but I'm not getting any result in the destination cell. Does anybody out there know how to fix it???

Sub Test()

Dim myDateTime As String

myDateTime = Year(Date) & Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) _
& Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2)

myDateTime = Range("Q1").Select

End Sub

Thanks for reading

Bert

Re: Using Input Boxes to build worksheets

Hello again

I’ve managed to stumble through (somehow) and add a loop that works and the autostart – but does anybody know how the data that is input into the input boxes can be checked for a certain character ( a comma) and a warning flashed up if input anywhere in the string??

Re: Using Input Boxes to build worksheets

Thanks Shades much appreciated - that does the job. I've tried to add a couple of extra parts - Autorun from opening the doc and a loop to repeat the input of the three 'Add' fields - but if constantly chucks up error messages. Do you or anyone else know how to add these features correctly to your code?

Re: Using Input Boxes to build worksheets

Sorry Shades - Friday afternoon and running on empty! Hopefully this one will be better...

Hi

Having problems with input boxes. I'm trying to creat 3 input boxes that will prompt the user to input 'Add1' in cell A1 'Add2' in cell B1 and 'Add3' in cell C1. Then return back to the first prompt box ready for further info - but this time, if it finds info in cell A1 it needs to move down to A2....and so on. Just about down the easy bit and have the three input boxes working - but unfortunatley that's it. Any ideas guys??

Thanks for reading

B

Have attached my limited success!

The problem is that each month could contain hundreds of different and/or the same project number month on month. So Jan may contain for example 400 different projects, Feb may also contain 400 but one of them is different. without searching carefully through the list of Feb I'm not going to know which has changed and what it has changed to. The results in you example column J would now sum the 400 Jan and Feb projects (a total of 401 different project). Do you know what I’m getting at or I’m I missing your point? Thanks for replying

B

Hi Shark

No I don't have a full list - new project are being created all the time. By the end of the year it could be several hundreds. The info is already being dragged into the workbook from several other workbooks - just thought that there might be an automated solution to keep a running total of the projects as and when they appear month on month. Will look at the best way to use a pivot table, but if anyone else can think of a different method then please let me know.

Thanks for your help Shark

B

Thanks Thomach

I think that my example may be a little misleading though. The example I sent you with projects as letters of the alphabet was just an example - the projects could be could absolutely anything. In the example you have attached it includes projects (letters) that were not included in the table of information (therefore summing a total of zero) - Does that make sense?? Because of the random nature of the project names it cannot be done this way.

Do you have any ideas?

Thanks again

B

Hi

Trying to find the best way to add together 'matching ' information. Month on month across a year, information is built up for various 'projects'. At the end of a year the totals for each project needs to be tallied up. Problem is that these projects don't necassarily fall in the same row and may in fact only appear once in a year. I'm guessing that somehow a running total of which project have been entered is needed, and then a search is required to sum the value of every instant that it appears. I've attached (I hope!) a small example to help explain my ramblings..

Any ideas???

Thanks v much
B

Thanks Jindon

Think that I may have been overcomplicating things!

Cheers
B

Hi

Need some help in turning a time (in fact a sum hours and minutes) into a decimal. I used this =HOUR(A2)+(MINUTE(A2)/60) which works fine until the total hours exceeds 24 then the HOUR command ignores the first 24 hours and works on the remainder ie 25:00:00 (25hrs) = 1 user the above equation.

Thanks for reading

B

Thanks so much. Works magnificiently! I don't suppose that you can recommend any good books/learning aids for VB beginners??

Thanks again

Bertie

Hi

Thanks for replying. Hope this is what you need to help. The code is attached to the 'Update' button on worksheet CALC1.

B

Please, please can anybody help with this problem. I had a command button successfully copying and a pasteing some info then deleting rows that match a given criteria. I decided to add a couple more actions, but from where the new code starts - Columns("H:H").Select - it flags up an error. Any ideas?? Thanks for reading.

B

Private Sub CommandButton1_Click()

Columns("G:O").Select
Selection.Copy
Sheets("Warehouse").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Range("I1").Select
ActiveCell.FormulaR1C1 = "Checked"
Range("I2").Select
ActiveCell.FormulaR1C1 = "?"
Range("A1:I2").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = clCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Columns("A:I").Select
Selection.Columns.AutoFit
Range("A1").Select
Dim r As Long
Application.ScreenUpdating = False
With Worksheets("Warehouse")
For r = .UsedRange.Rows.Count To 1 Step -1
If .Cells(r, "H").Value = "0" Then .Rows(r).Delete
Next
End With
Application.ScreenUpdating = True

End Sub

Genius!!

Works a treat, thanks so much for your time. Can stop pulling my hair out now.

Bertie

Thanks Jindon

Your part works fine but when I try to combine with the Do...Loop that gollem suggested I have problems. I changed 'Do while..' to 'Do until..',(but both had the same error message) would this be a problem, I'm being told Loop without Do (?) - also the command button needs to be on Worksheet A not B which is why I'm trying to activate a sheet before starting the loop, not sure if thats a problem.

Any ideas?? Code below..

Private Sub CommandButton1_Click()
Sheets("Delivery Data").Select
Do Until ActiveSheet.Cells(1, 1).Value = ""
Sheets("Delivery Note").Select
Range("A1:N37").Select
Selection.PrintOut Copies:=1, Collate:=True
ActiveWindow.ScrollRow = 1
Range("A1").Select
With Sheets("Delivery Data")
.Rows("1").ClearContents
.Rows("2").Copy Destination:=.Rows("1")
Application.CutCopyMode = False
.Rows("2").Delete
Sheets("Delivery Note").Select

Loop
End Sub

Thanks again

Bertie