Posts by josh1981

This is it:

Dim fnd As Range
    On Error Resume Next
    Set fnd = Range("B:B").Find(Range("A397").Value)
    fnd.Resize(24, 8).Copy [K401]

fnd is a date, but it is a formula referencing another cell. The entire range is basic formulas referencing other cells and I would like the values of those cells to be pasted instead of the formulas being pasted.

Hi
How to change the following to paste values

fnd.Resize(24, 8).Copy [K401]

Thanks.

Yes, that works. Thanks very much.

Hi. I have VBA code to send emails. I am having a problem with a long list of emails and I would like to line space between them. I have tried the example below which does not work. I have also tried referring to range.("A1:A50") which does not work. Any help is appreciated. Thanks.

.To = "[email protected];" & _
"[email protected];" & _
"[email protected];" & _
etc. etc.....

.To = ThisWorkbook.Sheets("Sheet1").Range("A1:A50")

Hi. When you hover your mouse pointer over a line graph it displays the value. I was wondering if it is possible having that value automatically inserted into a formula when you hover over the graph or by clicking on the graph. Thanks.

Hi. When you hover your mouse pointer over a line graph it displays the value. I was wondering if it is possible having that value automatically inserted into a formula when you hover over the graph or by clicking on the graph. Thanks.

That works. Thanks a lot.

I get #Value - A value used in the formula is of the wrong data type.
However, after playing around with some numbers and using "'Sheet2'!" in the formula, I get an answer. So don't know what the problem is with using "'Sheet1'!".

Hi.
In regards to the formula =ADDRESS(MATCH(M13,'Sheet1'!K1:K23,0),11)

I would like the value of K1 in the above formula to equal the cell value of cell G13 in Sheet2. If it helps, the cell value on cell G13 in Sheet2 is $K$6.
Thanks.

Re: Wrong format in FIND box

What I mean by the FIND box, is the dialogue box that comes up when you select the find and search function on the far right of the HOME tab in the ribbon. The macro is using this to search for the value in cell D199. I know this as when I have run the macro, I can see the value when I open the 'find and search' dialogue box, only the format is different. E.g. if the value of D199 is 28-12-15, the value in the dialogue box is shown as 12/28/2015.
I really believe this is causing the problem as when I change the format of all dates to general number, E.g. 28-12-15 = 42625 as a general number, the code works as the value is the same and is able to be found.
Another weird thing is that this problem only occurs on occasion. Most of the time the macro works.

Hello.
With the following code:

Sub Tides()
Application.ScreenUpdating = False
Worksheets("Prep").Select
    Dim fnd As Range
    On Error Resume Next
    Set fnd = Range("B:B").Find(Range("D199").Value)
    fnd.Resize(14, 3).Copy [I2]
        
Application.ScreenUpdating = True
End Sub

The format for all my dates are d-mm-yy, but in the FIND box it comes up as mm/dd/yyyy. Because of this the value D199 is not found. I have checked all my regional settings and they are correct.
If I change all dates to GERNERAL format, the code works, but I want the date format.
Any help would be much appreciated. Thanks.

Re: Macros work on one computer, but not another

yes

Can someone please tell me why a macro might work on one computer but not another. Both computers are using the same version of excel and windows. There are no error messages showing on the computer that the macro does not work on.
Thanks.

Re: runtime error 13 type mismatch

Hi S O. Thanks for your reply. I have just realised cell T13 had a N/A value which was causing the problem. All fixed now.

For CDO code. Can someone please explain why I am getting this error with the following part of the code highlighted:

StrBody = "PLEASE DO NOT REPLY TO THIS EMAIL" & vbNewLine & vbNewLine & _
              "Please see bellow estimated total cargo for the DUKC MSD" & vbNewLine & vbNewLine & _
              "DUKC MSD    " & ThisWorkbook.Sheets("Drafts").Range("U81") & vbNewLine & _
              "Equivalent total metric tonnes  " & ThisWorkbook.Sheets("Drafts").Range("T13") & vbNewLine & vbNewLine & _
              "Regards" & vbNewLine & _
              "Draught Surveyor" & vbNewLine & _
              ThisWorkbook.Sheets("Report").Range("G60") & vbNewLine & _
              ThisWorkbook.Sheets("Advice").Range("B5") & vbNewLine & _
              ThisWorkbook.Sheets("Advice").Range("B6")

Tks in advance.

Re: Round value in CDO strbody

Thanks. That works. How bout if I want to format it as a percentage?

Please see line 3 and 4 of below code. Although the cell values from cells J16 and I70 are rounded off in the spreadsheet to two decimal places, the values are displayed on the email to many decimal places. I have tried a round formula in the code with no luck. Any suggestion would be much appreciated.

StrBody = "PLEASE DO NOT REPLY TO THIS GMAIL ADDRESS" & vbNewLine & vbNewLine & _
              "Please see attached performance report" & vbNewLine & vbNewLine & _
              "80% belt error  " & ThisWorkbook.Sheets("Drafts").Range("J16") & vbNewLine & _
              "Final belt error  " & ThisWorkbook.Sheets("Prep").Range("I70") & vbNewLine & vbNewLine & _
              "Regards" & vbNewLine & _
              "Draft Surveyor" & vbNewLine & _
              ThisWorkbook.Sheets("Prep").Range("D79") & vbNewLine & _
              ThisWorkbook.Sheets("Prep").Range("E79")

Re: using cell value as part of range in conditional formatting formula

Thanks Mike.

Re: using cell value as part of range in conditional formatting formula

Thanks etaf. Put range as text string in cell Sheet2!D19 and tweaked formula to

=IF(AND($U$15=2,COUNTIF(INDIRECT(Sheet2!$D$19),B21)>2),TRUE,FALSE)

which does the job.

Hi. As per the title, I am using the following formula:
=IF(AND($U$15=3,COUNTIF($B$21:INDIRECT(Sheet2!D17),B21)>3),TRUE,FALSE)

where Sheet2!D17 cell value = B33. Excel will not let me do this saying that I may not use reference operators. Is there another way around this? FYI the range in the CF formula needs to be dependant on the number of cells that have values in them.