Disappointed not to have received a response at the other forum months ago, This file's a bit different but a similar formula in cell L6 gives a much more realistic result:
=ROUND(AVERAGE(IF(NOT(ISBLANK($C6:$K6)),$C6:$K6-DATE(LEFT($C$3:$K$3,4),1,0))),0)which may have to be array-entered (with Ctrl+Shift+Enter instead of plain Enter) depending on your version of Excel.
Format d mmm to make it clear.
vbaexpress.com is down at the moment; I'll try to post something there tomorrow for you to try…
Ah yes, at https://www.excelforum.com/exc…om-various-workbooks.html
edit post posting: Ha! you beat me to it.
Worth reading about all this cross posting stuff:
http://www.excelguru.ca/content.php?184
to ensure good responses in the future
Could you say where (provide links if you can) you posted? (I've already responded to your thread at vbaexpress: http://www.vbaexpress.com/foru…ns-from-various-workbooks .)
Cross posted without links all over the place,
Late I know, it seems the file must not be .xlsb; a .xlsx or .xlsm file will show both sheets and tables:
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"2019-08-29_225106.png","data-attachmentid":1222483}[/ATTACH]
Re: Formula: Test for overlapping number ranges
This seems to give similar results:
=(MIN(B1,B2)-MAX(A1,A2))>=0
Re: Question Inserting Rows based on cell differences and interpolating in between
as well as:
http://www.mrexcel.com/forum/e…nterpolating-between.html
also cross posted at
http://www.vbaexpress.com/foru…tween&p=348064#post348064
https://groups.google.com/foru…l.programming/gjmgjk3KyU8
http://www.excelbanter.com/showthread.php?t=451909
Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected
Where to put the Stop line? Well perhaps here:
[COLOR="#0000FF"]obj.settext str
obj.PutInClipboard
Selection.Delete Shift:=xlUp
[COLOR="#FF0000"]Stop[/COLOR]
Sheets.Add After:=Sheets(Sheets.count)[/COLOR]
When it errors on the stop line you can continue by pressing F5 on the keyboard. It just confirms that the macro has been entered when you think it has. Remove the stop line when it's done its job as it gets in the way.
I put your CopyToClipBoardAndDelete macro into a module of my Personal.xlsb as you have and assigned it a keyboard shortcut.
I did the same with this:
Sub qqqq()
Selection.Insert Shift:=xlDown
End Suband assigned it another keyboard shortcut.
I then selected non-contiguous rows, ran the first, chose a location by selecting a row elsewhere and called the second macro with the other keyboard shortcut and it pasted (inserted) the rows. It did this whether it was the same worksheet, a different worksheet or even a different workbook.
Re: Saving result from userform textbox into worksheet column
Roy,
prompted by your last post I tried it and noticed a few things here (UK locale); first is that it produced a string (more on this later), second was that when Excel reported that it was a number formatted as text with its little green triangle, and I used the on-sheet dropdown (little exclamation point next to error) to Convert to Number, it did so but retained the GBP sign and formatted the cell as Currency (with negative values in red too). It was this second operation that I was trying to replicate with a one-liner in vba. I got nowherere.
I asked a couple of people to send me their files after they had run following the single line on a virgin sheet in their locales (Euro and US).
Range("A1") = FormatCurrency(-33.33)
The results were a bit of a surprise:
In the UK it produces a pound sign string which on convert-to-number with the on-screen dropdown produces a number in a GBpound formatted cell.
In a Euro locale (Patrick's) it produces a plain number in the cell, no formatting, no currency symbol (what's the blinking point?)
In the US (?) (Brian's) it produces a full currency-formatted cell with a number (no strings here!) from the off.
So with that very variable behaviour I doubt I'll ever use FormatCurrency!
There's a thread at the Excel-L group at http://peach.ease.lsoft.com July 2016 Week 4 called Convert to Number
Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected
Well I'm not yet satisfied your code is running when you think it is.
Did you try my suggestion: Put a [COLOR="#0000FF"]Stop [/COLOR]somewhere in the CopytoClipboardandDelete2 macro to make sure it's called at all when you think it's being called. ?
Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected
I tested again (without altering your code) and it worked.
So now a couple of questions:
1. Where have you put the code?
2. How do you open the text file?
Suggestion: Put a [COLOR="#0000FF"]Stop [/COLOR]somewhere in the CopytoClipboardandDelete2 macro to make sure it's called at all when you think it's being called.
Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected
I did test this before responding. The only difference was that I commented-out one line from your macro, the Activewindow.selectedsheeys.visible=False one. Unfortunately I can't test right now as I'm not near a computer.
Re: Unable to insert (rows) from Clipboard into needed rows below a variable selected
It's a macro with one line:
Selection.Insert Shift:=xlDown
Re: Saving result from userform textbox into worksheet column
Private Sub CommandButton2_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(" daysworked")
x = Me.Lblrow
With ws.Cells(x, 20)
.Value = CDbl(UserForm2.Payment)
.NumberFormat = "$#,##0.00"
End With
UserForm2.Hide
End SubRe: Add data to sheet from userform with row number specified in textbox.
Supply the file and I'll put it in for you.
Of course, the [COLOR="#0000FF"]=… [/COLOR]needs to be adjusted to whatever you want to put in that cell.
Re: Add data to sheet from userform with row number specified in textbox.
sheets("sheet5").cells(clng(txtrow.value),"A").value =…Re: My code to export a table into excel SUDDENLY stopped working.
It's OK here, just very slow.
Is it a fresh sheet everytime you use it? If not, perhaps Refresh instead of creating a new query.
Re: Pull the data from different closed workbooks paste into master vba
cross posted, all over the place, again.
Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells
cross posts:
http://www.vbaexpress.com/foru…-Value-of-Two-Other-Cells
http://www.excelguru.ca/forums…d-on-Value-in-Two-Columns