JeremyGraham95

New Member
Joined
Jan 12, 2016
Messages
19
I am working on an inventory project and have 2 problems that i cannot find a solution for.


1. The first is i need to select the first empty cell in the first row and fill it with the current date. I have the code for the date working great and the code works fine the first time, but after that it does not seem to benefit me at all.

2. My second problem, the more complex problem. after completing the step above, i want the code to select the entire column of data starting with the third row, and also select the previous 3 columns, a total of 4 columns will be selected. and on top of this, i dont want it to select the first column. so with this being a brand new spreadsheet, the first 3 occurences would benefit from that code but would still be useful.


This is the code i have written:


Worksheets("TRENDS").Activate

SaveColNdx = Cells(1, Columns.Count).End(xlToLeft).Column
If SaveColNdx > 1 Then SaveColNdx = SaveColNdx + 1


With ActiveCell
.Value = Date
.NumberFormat = "mm/dd/yyyy"



*** later on i would like it to hide all columns except the first column ( info column) and the 6 columns with data that are closest to the right end of the sheet.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
What's happening might be that the first time you run it, A1 is empty and SaveColNdx = 1.
The second time that you run it A1 has a date in it, but agains SaveColNdx = 1, so it gets overwritten.

You could change the test to

Code:
If Cells(1, SaveColNdx).Value <> vbNullString Then SaveColNdx = SaveColNdx + 1

Or you could put a header (or a space or a something) in A1 and always increment SaveColNdx without testing to see which column it is.
 

JeremyGraham95

New Member
Joined
Jan 12, 2016
Messages
19
I tried that code and it was now placing the date in a1 each time. its not moving over to the next empty cell in the row.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,993
Try

Code:
With Sheets("TRENDS").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
    .Value = Date
    .NumberFormat = "mm/dd/yyyy"
    
    If 7 < .Column Then
        Range(.Parent.Range("B1"), .Offset(0, -6)).EntireColumn.Hidden = True
    End If
End With
 
Last edited:

JeremyGraham95

New Member
Joined
Jan 12, 2016
Messages
19

ADVERTISEMENT

That code works very well however it doesnt do the first date, which is not a problem.

the next thing i am adding to the code is to take those 6 columns, with data that arent hidden, and make a trend line in a new sheet called "sheet 2". i want the line to tell me how many of each item(row) i have used between columns. subtract the the first column from the second, second from the third and so on.
 

JeremyGraham95

New Member
Joined
Jan 12, 2016
Messages
19
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long


Set source = Sheets("Order Summary")
Set destination = Sheets("TRENDS")


'find empty Column (actually cell in Row 1)'
emptyColumn = destination.Cells(1, destination.Columns.Count).End(xlUp).Column
If emptyColumn > 1 Then
emptyColumn = emptyColumn + 1
End If


source.Range("B2:B127").Copy destination.Cells(1, emptyColumn)
 

JeremyGraham95

New Member
Joined
Jan 12, 2016
Messages
19
i am having trouble getting this piece of code to work. it says that there is an object error in the last row of the code. can anyone help me out???
 

Watch MrExcel Video

Forum statistics

Threads
1,130,359
Messages
5,641,647
Members
417,229
Latest member
BODYCOTE

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
Top