# Search:

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

Page 1 of 6 1

1. ## Thread: Extract and arrange the numbers small to larger values

by Rick Rothstein
Replies
16
Views
152

### Re: Extract and arrange the numbers small to larger values

Am I remembering correctly from past postings of yours... except for the zero values, aren't your numbers in each row of Columns E:N always in sorted numerical order from left to right? If so, this...
2. ## Thread: Problem with VBA Loop

by Rick Rothstein
Replies
11
Views
377

### Re: Problem with VBA Loop

Try changing that line of code to this and see if it works correctly...

sumall = sumall + Val(alpha_1y(i, 1))
3. ## Thread: formatting a date

by Rick Rothstein
Replies
18
Views
464

### 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...
4. ## Thread: formatting a date

by Rick Rothstein
Replies
18
Views
464

### 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\/\/"))
5. ## Thread: formatting a date

by Rick Rothstein
Replies
18
Views
464

### Re: formatting a date

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

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

by Rick Rothstein
Replies
5
Views
138

### Re: Change how date is converted.

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

by Rick Rothstein
Replies
5
Views
138

### 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...
8. ## Thread: Adding a +1 to a Row when dragging across Columns

by Rick Rothstein
Replies
2
Views
77

### 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)
9. ## Thread: Numbering fixed amount of Rows

by Rick Rothstein
Replies
4
Views
64

### 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") =...
10. ## Thread: Numbering fixed amount of Rows

by Rick Rothstein
Replies
4
Views
64

### 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 +...
11. ## Thread: excel 2002 convert numbers to mathematical

by Rick Rothstein
Replies
30
Views
1,340

### 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)...
12. ## Thread: Adding Numbers with Text in Cell

by Rick Rothstein
Replies
3
Views
147

### Re: Adding Numbers with Text in Cell

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

Function AddNums(ParamArray NumOrRng())
Dim V As Variant, VV As Variant
For Each V In NumOrRng
If VarType(V) < vbArray Then...
13. ## 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
14. ## Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
127

### 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...
15. ## Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
127

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

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

by Rick Rothstein
Replies
12
Views
127

### 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...
17. ## Thread: making excel recognize it's 20XX not 19XX

by Rick Rothstein
Replies
12
Views
127

### 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?
18. ## 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...
19. ## Thread: Cell recognize positive or negative value then adds certain texts to the 2 possibilities

by Rick Rothstein
Replies
9
Views
107

### 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""")
20. ## Thread: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

by Rick Rothstein
Replies
7
Views
83

### 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 .
21. ## Thread: Replacing an On Error with an If, regarding copying cells that may not exist after a filter

by Rick Rothstein
Replies
7
Views
83

### 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...
22. ## Thread: VBA Code - Delete rows once column A no longer numbers

by Rick Rothstein
Replies
2
Views
45

### 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 =...
23. ## Thread: find last occurence of x

by Rick Rothstein
Replies
8
Views
201

### 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...
24. ## Thread: find last occurence of x

by Rick Rothstein
Replies
8
Views
201

### Re: find last occurence of x

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

by Rick Rothstein
Replies
8
Views
201

### Re: find last occurence of x

Try this in your macro...

Sheets("Sheet2").Columns("G").Find("X", , , xlWhole, , xlPrevious, False, , False).Offset(7) = "X"
Results 1 to 25 of 150
Page 1 of 6 1