# Search:

Type: Posts; User: MartinLiss

Page 1 of 13 1

1. ## Thread: [RESOLVED] Need help with a formula

by MartinLiss
Replies
10
Views
425

### Re: Need help with a formula

Thanks! With a couple of small changes it gives me what I need.

=IF(OR(D7="",NOT(ISERROR(FIND("day",TEXT(D7,"dddd"))))),1,NA())
2. ## Thread: [RESOLVED] Need help with a formula

by MartinLiss
Replies
10
Views
425

### Re: Need help with a formula

Thanks! with a couple of small changes it gives me what I need.

=IF(OR(D7="",NOT(ISERROR(FIND("day",TEXT(D7,"dddd"))))),1,NA())
3. ## Thread: [RESOLVED] Need help with a formula

by MartinLiss
Replies
10
Views
425

### Re: Need help with a formula

It really doesn't work with my data. I've attached a small sample workbook.
4. ## Thread: [RESOLVED] Need help with a formula

by MartinLiss
Replies
10
Views
425

### Re: Need help with a formula

A formula would be nice but I was able to do it with VBA.

Dim lngLastRow As Long
Dim lngRow As Long
lngLastRow = Range("D1048576").End(xlUp).Row

For lngRow = 7 To lngLastRow
If...
5. ## Thread: [RESOLVED] Need help with a formula

by MartinLiss
Replies
10
Views
425

### Re: Need help with a formula

That's, if it's changed to =IF(OR(D1="",NOT(ISERROR(FIND("day",D1)))),1,NA()) it's almost what I need. I believe that formula look for exactly the word day whereas I'd like it to look for day in a...
6. ## Thread: [RESOLVED] Need help with a formula

by MartinLiss
Replies
10
Views
425

### [RESOLVED] Need help with a formula

If column 'D' contains the substring "day" (without the quotes) or is blank, the result should be 1, otherwise the result should be NA().
7. ## Thread: VB - An ActiveX control which supplies a pair of Yes/No Option Buttons

by MartinLiss
Replies
2
Views
6,761

### Re: VB - An ActiveX control which supplies a pair of Yes/No Option Buttons

That is a nice trick, so thanks and I'm adding it to my code snippets library. I don't have any plans to modify the existing code however.
8. ## Thread: Excel: Dynamic Named Range for Row

by MartinLiss
Replies
3
Views
287

### Re: Excel: Dynamic Named Range for Row

Thanks. I should have mentioned that I want to avoid VBA in this case.
9. ## Thread: Excel: Dynamic Named Range for Row

by MartinLiss
Replies
3
Views
287

### Excel: Dynamic Named Range for Row

I currently have the following named range formula

=Sheet1!\$L\$5:\$N\$5

which is for a row and it works just fine fine there are just 3 values, starting in the row at cell L5 to cell N5, but what...
10. ## Thread: [RESOLVED] Excel Formula Needed

by MartinLiss
Replies
1
Views
205

Never mind.
11. ## Thread: [RESOLVED] Excel Formula Needed

by MartinLiss
Replies
1
Views
205

### [RESOLVED] Excel Formula Needed

I currently have this formula

=IF(X4<1,"Expired",IF(X4>=0,"Active",""))

where column 'X' is the result of subtracting two dates. I'd like the formula modified so that if X is blank then blank...
12. ## Thread: [RESOLVED] Row numbers of filtered rows in Excel

by MartinLiss
Replies
2
Views
294

### Re: Row numbers of filtered rows in Excel

Thanks but that doesn't work for me. This does,

With Sheets("MySheet")
For lngRow = 4 To .Range("A1048576").End(xlUp).Row
If Not IsEmpty(.Cells(lngRow, "A")) Then
If...
13. ## Thread: [RESOLVED] Row numbers of filtered rows in Excel

by MartinLiss
Replies
2
Views
294

### [RESOLVED] Row numbers of filtered rows in Excel

I'd like to be able to determine the row numbers of the visible rows after autofilter is applied to a worksheet. How can I do that?
14. ## Thread: RegEx to match two out of three digits

by MartinLiss
Replies
5
Views
433

### Re: RegEx to match two out of three digits

Thanks Ecniv. How would I return the actual matching characters rather tham True/False. In other words
017 ==> 17
637 ==> 67
619 ==> 61

BTW here's part of the VBA I use now which as it turns...
15. ## Thread: RegEx to match two out of three digits

by MartinLiss
Replies
5
Views
433

### Re: RegEx to match two out of three digits

Thanks Ecniv. How would I return the actual matching characters rather tham True/False. In other words
017 ==> 17
637 ==> 67
619 ==> 61
16. ## Thread: RegEx to match two out of three digits

by MartinLiss
Replies
5
Views
433

### RegEx to match two out of three digits

I'm trying to analyze some numbers to find two-digit number matches between two, three-digit numbers. For example if I have "617" in range("B1") is there a RegEx statement that would say that "017",...
17. ## Thread: Using 'Set' in Excel to create a worksheet object

by MartinLiss
Replies
2
Views
330

### Using 'Set' in Excel to create a worksheet object

What are the advantages or disadvantages of using Set wsData = Sheet1 as opposed to Set wsDtata = Sheets("Data")?
18. ## Thread: [RESOLVED] I've never seen this before

by MartinLiss
Replies
9
Views
508

### Re: I've never seen this before

I found that just as (2) puts the value 1 row below the last cell, (5) puts it 4 below, even if there are black cells between previous enttries.
19. ## Thread: [RESOLVED] Excel: Synch two userform listboxes

by MartinLiss
Replies
2
Views
373

### Re: Excel: Synch two userform listboxes

I've given up on this and instead replaced the listbox with a listview.
20. ## Thread: [RESOLVED] I've never seen this before

by MartinLiss
Replies
9
Views
508

### Re: I've never seen this before

A perfect example why "magic numbers" are a bad idea. It never occurred to me that 3 was the magic number for xlUp. Other than that I think it's clever in that it finds the last row in the column and...
21. ## Thread: [RESOLVED] I've never seen this before

by MartinLiss
Replies
9
Views
508

### Re: I've never seen this before

A perfect example why "magic numbers" are a bad idea. It never occurred to me that 3 was the magic number for xlUp. Other than that I think it's clever in that it finds the last row in the column and...
22. ## Thread: [RESOLVED] I've never seen this before

by MartinLiss
Replies
9
Views
508

### [RESOLVED] I've never seen this before

Can anyone explain what the 2 and 3 parameters in this line of code represent?

Range("A" & Rows.Count).End(3)(2).Value = "blah"
23. ## Thread: Excel userform listbox vertical scroll bar problem

by MartinLiss
Replies
2
Views
333

### Re: Excel userform listbox vertical scroll bar problem

You're right but wouldn't I need to know their height? In my VB6 days it was 40 and I'll see if I can work that in.
24. ## Thread: Excel userform listbox vertical scroll bar problem

by MartinLiss
Replies
2
Views
333

### Excel userform listbox vertical scroll bar problem

This is driving me nuts.

On userform I have a dynamic listbox and the listbox is in a frame which is shorter than length of the listbox. For complicated reasons I don't want the vertical scrollbar...
25. ## Thread: [RESOLVED] Excel: Synch two userform listboxes

by MartinLiss
Replies
2
Views
373

### [RESOLVED] Excel: Synch two userform listboxes

I have an Excel userform with two, form-wide, multi-column listboxes. The data in both cases is two wide to be shown completely, so each has a horizontal scrollbar. I'd rather not widen the form and...
26. ## Thread: [RESOLVED] Copy formula from another sheet

by MartinLiss
Replies
2
Views
315

### Re: Copy formula from another sheet

Thanks, it was obvious when you suggested it, it just never occurred to me. I needed to use just \$I9 and \$J9.
27. ## Thread: [RESOLVED] Copy formula from another sheet

by MartinLiss
Replies
2
Views
315

### [RESOLVED] Copy formula from another sheet

I have a long, complex, formula in Range(T2) on one sheet that refers to columns "I2" and "J2" and I have made T2 into a named range called MyNR. I want to copy that formula to column "S9" on a...
28. ## Thread: [RESOLVED] VBA to delete CELLS and move up

by MartinLiss
Replies
2
Views
380

### MsOf10Re: VBA to delete CELLS and move up

I found the solution. Clear the visible cells and then sort!
29. ## Thread: [RESOLVED] VBA to delete CELLS and move up

by MartinLiss
Replies
2
Views
380

### MsOf10[RESOLVED] VBA to delete CELLS and move up

I have a column that shows filtered data. Is it possible to delete the visible cells rather than the whole row and move those cells up without using a loop?
30. ## Thread: [RESOLVED] Convert a hex number containing a decimal point to Decimal

by MartinLiss
Replies
7
Views
425

Thanks all.
31. ## Thread: [RESOLVED] Convert a hex number containing a decimal point to Decimal

by MartinLiss
Replies
7
Views
425

### Re: Convert a hex number containing a decimal point to Decimal

Thank you, I saw that but I was hoping to be able to do this that works with hex numbers that don't have a decimal point.

MsgBox CInt("&H" & HexValue)
32. ## Thread: [RESOLVED] Convert a hex number containing a decimal point to Decimal

by MartinLiss
Replies
7
Views
425

### Re: Convert a hex number containing a decimal point to Decimal

Thank you, I saw that but I was hoping to be able to do this that works with hex numbers that don't have a decimal point.

MsgBox CInt("&H" & HexValue)
33. ## Thread: [RESOLVED] Convert a hex number containing a decimal point to Decimal

by MartinLiss
Replies
7
Views
425

### [RESOLVED] Convert a hex number containing a decimal point to Decimal

If I go to this site https://www.rapidtables.com/convert/number/hex-to-decimal.html and enter .1300 (note the decimal) and click 'Convert' the result is 0.07421875. How do I do that in VB6? No...
34. ## Thread: Get value of cell in Datagrid selected row

by MartinLiss
Replies
7
Views
332

### Re: Get value of cell in Datagrid selected row

Nevermind. I fixed it.
35. ## Thread: Get value of cell in Datagrid selected row

by MartinLiss
Replies
7
Views
332

### Re: Get value of cell in Datagrid selected row

I missed that, sorry. I ran the MakeDB project and while I'm looking at the sample.mdb file in C:\Program Files\Microsoft Visual Studio\VB98, the main project's Connection.Open statement throws an...
36. ## Thread: Get value of cell in Datagrid selected row

by MartinLiss
Replies
7
Views
332

### Re: Get value of cell in Datagrid selected row

The sample database seems to be missing.
37. ## Thread: Get value of cell in Datagrid selected row

by MartinLiss
Replies
7
Views
332

### Get value of cell in Datagrid selected row

I have a datagrid on a form VB6 and in a textbox I'd like to display the value of the 3rd column of the datagrid row that the user selects. How can that be done?
38. ## Thread: [RESOLVED] Very strange problem with a Do loop inside a loop

by MartinLiss
Replies
8
Views
669

### MsOf10Re: [RESOLVED] Very strange problem with a Do loop inside a loop

Here's the solutionI came up with.

lngLastRow = Range("A1048576").End(xlUp).Row
lngRow = 5
Cells(lngLastRow + 1, "B") = "End"

With wsS.Columns("L:L")
Do Until Cells(lngRow, "B") =...
39. ## Thread: [RESOLVED] Very strange problem with a Do loop inside a loop

by MartinLiss
Replies
8
Views
669

### MsOf10Re: Very strange problem with a Do loop inside a loop

Thank you both. Si, I have to say that even after the many years that I've worked with VBA I never realized that, so thanks!
40. ## Thread: [RESOLVED] Very strange problem with a Do loop inside a loop

by MartinLiss
Replies
8
Views
669

### MsOf10Re: Very strange problem with a Do loop inside a loop

But the incrementing of lngLastRow (the upper bound of the loop) does work and it keeps pace with lngRow.
Results 1 to 40 of 500
Page 1 of 13 1

Featured