Search results

for odd columns: =SUM(IF(MOD(COLUMN(A1:D1),2)=1,A1:D1)) for even columns =SUM(IF(MOD(COLUMN(A1:D1),2)=0,A1:D1)) entered as an array formula CTRL+SHIFT+ENTER

VBScript code to print using notepad: Dim objFSO, objText, strText, strFile, objShell strFile=replace(WScript.ScriptFullName,WScript.ScriptName,"to_print.txt") strText="Test" 'message that should be printed Set objShell=WScript.CreateObject("WScript.shell") Set...

yes

=IF(LEN(C1)>0,IF(AND(C1>=0,C1<5),A1,""),"") or =IF(AND(C1>=0,C1<5,LEN(C1)>0),A1,"")

the code below assumes you have a check box called "Check Box 1" in "Sheet1" and hides rows 11 to 44 based on its value. You can create a Sub for each checkbox by right clicking on it, selecting "Assign Macro", then select "New". Sub CheckBox1_Click() Application.ScreenUpdating = False Dim...

He means the two buttons in the funnel (pyramids) chart worksheet.

If you are calculating percentage of target achieved, then the target has to be larger than the actual number and both should be positive. For this the formula is: =(Actual/Target)*100 If you have negative numbers, you can only calculate percentage change from actual to target. For this a...

Not sure if the code below helps but it's one way of doing it. In the Workbook_Open event put a call to HideSheets to run the macro when the workbook is opened. Sub HideSheets() Dim choice As Integer Dim strList As String Dim ws As Worksheet Do Until choice > 0 And choice < 6...

Try this for making up the list: Sub MakeList() Dim ws As Worksheet Dim r As Integer Dim c As Integer Dim strRange As String r = 1 c = 12 For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then r = r + 1 Worksheets("Summary").Cells(r, c) = ws.Range("C5") End...

To skip C5 if it's empty, change this line: If ws.Range("C5") <> "<>" Then to: If ws.Range("C5") <> "<>" and ws.Range("C5") <> "" Then

Can you post a sample of your data for which the formula doesn't work?

That might happen if you have two rows which have the same largest date.

=LARGE((IF(A:A=D1,B:B)),1)-LARGE((IF(A:A=D1,B:B)),2) the formula assumes all the unique ids are in column A, the dates are in column B and the unique id you are searching for is in D1 Should be entered as an array formula Ctrl+Shift+Enter

=IF(Stats!K37>=0.75,"Well Done",IF(Stats!K37<0.5,"Needs Improvement","Good Effort!"))

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myRange As Range Dim lastActive As Range Set myRange = Range("A1:Z100") Set lastActive = Target.Previous If Target.Previous.Column <= 25 And Target.Previous.Column >= 5 Then If...

Try the formula below. It's not a well written one but it seems to work on your test data. Few things you have to do though: 1. this formula goes into E3 and assumes column B has the dates 2. in the project dates and tasks sheet, the column (range) with the dates for a project should be...

Might happen when the module and the sub inside have the same name

I am not sure how the Valor variable fixes the % problem. What I did was to set the formatting of cell C1 to % and it worked. Sorry I forgot to post it in the original.

Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Address = "$C$1" Then .Offset(1, 0).Value = Val(.Offset(0, -2).Value) * Val(.Value) ElseIf .Address = "$C$2" Then .Offset(-1, 0).Value = Val(.Value) / Val(.Offset(-1, -2).Value) End If End With End Sub

Paste the following into a module. To run it select the cells you want to change, run the macro and enter new sum when it asks for it. Sub RangeChange() Dim objCell As Range Dim oldSum As Long Dim newSum As Long newSum = CLng(InputBox("Input sum")) oldSum = 0 For Each...

I think you need to add a column for the dates only and count the overlapping days for each date. Not sure if you can do it without additional columns.

this should do it Sub ActiveXTest() Dim ws As Worksheet For Each ws In Worksheets On Error Resume Next Worksheets(ws.Name).OptionButton1.Value = False On Error GoTo 0 Next End Sub

try this line: ws.shapes("OptionButton1").controlformat.value=checked this should work for option buttons which are added as form controls, if you added an activex option button, it will be different

Can't download the file, that's the first issue.

If you put objXLApp everywhere inside the With statement, it makes using the With statement pointless. Note the added ".activesheet" on the With line. Try this: <br /> Set objXLApp = CreateObject("Excel.Application")<br /> objxlapp.Visible=False<br /&#62...