# Search:

Type: Posts; User: Rick Rothstein; Keyword(s):

Page 1 of 6 1

1. ## Thread: formatting a date

by Rick Rothstein
Replies
18
Views
414

### Re: formatting a date

The key to the TEXT function (and cell formatting) is that in the pattern string certain characters have special meaning (for example, the "d" standing for the day number in a date as opposed to...
2. ## Thread: formatting a date

by Rick Rothstein
Replies
18
Views
414

### Re: formatting a date

I think this formula will do what you want...

=UPPER(TEXT(D4+F4-SIGN(B5&1)*C5/24,"\B\/ddhhmm\Z mmm\/\/"))
3. ## Thread: formatting a date

by Rick Rothstein
Replies
18
Views
414

### Re: formatting a date

If your formula is works for the OP, then I think this one will also...

=TEXT(SUBSTITUTE(A1,"-",""),"00000")
4. ## Thread: Change how date is converted.

by Rick Rothstein
Replies
5
Views
98

### Re: Change how date is converted.

How would you type in the date October 17, 2019 into a cell using only numbers and slashes?
5. ## Thread: Change how date is converted.

by Rick Rothstein
Replies
5
Views
98

### Re: Change how date is converted.

So your system's natural date order is d/m/y but you are getting dates with m/d/y date order and you want to preserve them, is that correct? If so, I do not think you will be able to do that with...
6. ## Thread: Adding a +1 to a Row when dragging across Columns

by Rick Rothstein
Replies
2
Views
74

### Re: Adding a +1 to a Row when dragging across Columns

This formula should produce the displayed results that you want when dragged across...

=COLUMNS(\$B1:B1)
7. ## Thread: Numbering fixed amount of Rows

by Rick Rothstein
Replies
4
Views
63

### Re: Numbering fixed amount of Rows

Actually, using Arthur's formula as a base, we can do the macro without using a loop...

Sub Deciles()
Range("B1:B62208") = [IF({1},CEILING(ROW(A1:A62208)/7776,1))]
Range("B62209:B77757") =...
8. ## Thread: Numbering fixed amount of Rows

by Rick Rothstein
Replies
4
Views
63

### Re: Numbering fixed amount of Rows

Here is a macro that will fill your range with constants rather than formulas...

Sub Deciles()
Dim R As Long
For R = 1 To 69985 Step 7776
Cells(R, "B").Resize(7776).Value = Int((R +...
9. ## Thread: excel 2002 convert numbers to mathematical

by Rick Rothstein
Replies
30
Views
783

### Re: excel 2002 convert numbers to mathematical

Are your "numbers" left justified? If so, they are text values. In order for that to be the case, I suspect you have some trailing non-breaking space attached to the numbers. Give this a try...

1)...

by Rick Rothstein
Replies
3
Views
145

### Re: Adding Numbers with Text in Cell

Here is a way to do this without using Regular Expressions...

Dim V As Variant, VV As Variant
For Each V In NumOrRng
If VarType(V) < vbArray Then...
11. ## Thread: If value is 12 then fill range 1-12 etc..

by Rick Rothstein
Replies
4
Views
51

### Re: If value is 12 then fill range 1-12 etc..

This macro should do what you want...

Sub EnterNumberSeries()
[C1].Resize([A1]) = Evaluate("ROW(1:" & [A1] & ")")
End Sub
12. ## Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
124

### Re: making excel recognize it's 20XX not 19XX

No, if you entered a date and the code "corrected" it for the year, your colleague's computer would show the corrected year (2045); however, if your colleague put a date into the worksheet using her...
13. ## Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
124

### Re: making excel recognize it's 20XX not 19XX

They certainly did bury it deep; however, the OP said in his opening post that...
14. ## Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
124

### Re: making excel recognize it's 20XX not 19XX

Give this workbook event code a try (it will automatically make all date years be in the 2000's when they are entered into a cell in Column F or G on the indicated sheets)...

Private Sub...
15. ## Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
124

### Re: making excel recognize it's 20XX not 19XX

In addition to Joe4's question above, is there a particular sheet and range that your dates can be in or are you wanting a general fix for any sheet in the workbook inside of any cell on the sheet?
16. ## Thread: Prevent first row to be deleted

by Rick Rothstein
Replies
2
Views
46

### Re: Prevent first row to be deleted

Maybe this in place of what you posted...

With .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
.AutoFilter Field:=1, Criteria1:="xxxx"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End...
17. ## Thread: Cell recognize positive or negative value then adds certain texts to the 2 possibilities

by Rick Rothstein
Replies
9
Views
106

### Re: Cell recognize positive or negative value then adds certain texts to the 2 possibilities

Assuming your value (percentage) is in cell A1, give this formula a try...

=TEXT(A1,"""Overestimate"" 0%;""Underestimate"" 0%;""Spot On""")
18. ## Thread: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

by Rick Rothstein
Replies
7
Views
82

### Re: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

If your filter is hiding every row in the range being examined, the you code will stop with a "No Cells Found" error. See what I wrote in Message #4 .
19. ## Thread: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

by Rick Rothstein
Replies
7
Views
82

### Re: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

The On Error code lines in Norie's code do not persist and should not affect any other code. With that said, if you can guarantee that at least one row will be visible when you run the macro, you can...
20. ## Thread: VBA Code - Delete rows once column A no longer numbers

by Rick Rothstein
Replies
2
Views
43

### Re: VBA Code - Delete rows once column A no longer numbers

Does this macro do what you want...

Sub DeleteRubbishRows()
Dim LastNumberRow As Long, LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastNumberRow =...
21. ## Thread: find last occurence of x

by Rick Rothstein
Replies
8
Views
190

### Re: find last occurence of x

Will a cell equal to TODAY() appear only once in Column A or could today's date appear more than once in Column A?

If more than once, are we supposed to put the "X" in Column G for only the last...
22. ## Thread: find last occurence of x

by Rick Rothstein
Replies
8
Views
190

### Re: find last occurence of x

Make what cell "X"... the one with in Column A with the date in it?
23. ## Thread: find last occurence of x

by Rick Rothstein
Replies
8
Views
190

### Re: find last occurence of x

Sheets("Sheet2").Columns("G").Find("X", , , xlWhole, , xlPrevious, False, , False).Offset(7) = "X"
24. ## Thread: Form button to control checkbox selection

by Rick Rothstein
Replies
3
Views
60

### Re: Form button to control checkbox selection

Or this (assuming the CheckBox is initialized with either xlOn or xlOff to start with)...

Sub Hk()
With ActiveSheet.CheckBoxes("Check Box 2")
.Value = (xlOn + xlOff) - .Value
...
25. ## Thread: Excel Round Function

by Rick Rothstein
Replies
2
Views
73

### Re: Excel Round Function

I think this formula may do what you seem to be indicating you want...

=MROUND(A1+125,250)
Results 1 to 25 of 150
Page 1 of 6 1