Posts by Egad

This is a method of referencing cells that I haven't seen mentioned in the forums (some one will correct me if I'm wrong)
It's saved me a few times and is a great way of looping thru a range of cells in code or picking an offset cell value.

range("b2:d3") (1,1).value = (value of b2)
range("b2:d3") (2,2).value =(value of c3)
range("b2:d3") (6,1).value= (value of b7)
range("a1:a10") (2).value = (value of a2)

Credit to: http://www.cpearson.com/excel/cells.htm

Punch cards. I used to be an IBM 1401/7074 operator feeding it massive decks of cards. The insurance co. I worked for had a whole room of key-punch operators. The IT schools at the time were all running ads about the wonderful career opportunities of key-puch operators. A different era.

If you use this approach be mindful of the implications on other users:
"The ExclusiveAccess method saves any changes you've made to the workbook and requires other users who have the workbook open to save their changes to a different file."
I assume the macro wouldn't need exclusive access for too long. I am unclear if other users would have to "save changes to another workbook" only during the macro life-span or afterwards also.

I have the same problem importing from CSV sheets and use a conversion macro similar to this.
This macro converts the cells in place but can be modified to place into an adjacent column.

Sub DateCon()
For x = 1 To 100
If IsDate(Cells(x, 1)) Then
Cells(x, 1).Value = "'" & Month(Cells(x, 1).Value) & "-" & Day(Cells(x, 1).Value)
Else
Cells(x, 1).Value = Cells(x, 1).Value
End If
Cells(x, 1).NumberFormat = "@"
Next x
End Sub

Hi & welcome to the Forums.
Try this in b10:
=HLOOKUP($B$8,$B$1:$F$5,3,FALSE)

Welcome to the Forums.
Format-Cells-Alignment-WrapText will work if you are entering data or copy-pasting data but not if you are linking data (ie in cell b10 you have formula =a1)

Yes, asap-utilities is great, I'd recommend it also, but it is an add-in and I am certain that none of my clients have this installed. It makes my life less complicated to put relevant UDFs into a spreadsheet module instead of asking clients to download A-U.

[quote=IF(ISNA(VLOOKUP(E2,A$2:B$4,2,FALSE)),"Not Found",VLOOKUP(E2,A$2:B$4,2,FALSE))
[/quote]

The long error trapping code required in Excel has always bugged me, especially having to write the vlookup twice as in the example above.

That's why I created & use this UDF function:

Place this code into a VBE module:

Function ErrFix(formula, result)
If IsError(formula) Then
ErrFix = result
Else
ErrFix = formula
End If
End Function

..and on the spreadsheet use this formula:
=ErrFix(formula, error result)

ie:
=ErrFix(VLOOKUP(F5,C2:D12,2,FALSE),"Not found")

It can also be used to trap #div/0 errors(returns a 0 or blank if e3 is 0.)
=ErrFix(E2/E3,0)
=ErrFix(E2/E3,"")

Hi, do you mean that you want to check the differences between 2 workbooks?

OK, I think I have it now, I hope so, let me know...

=AVERAGE(OFFSET(INDIRECT("average!a"&MATCH(2,average!A1:A100,0)+CELL("row",average!A1:A100)-1),0,4,50,1))

or

=AVERAGE(OFFSET(INDIRECT("average!a"&MATCH(2,rangename,0)+CELL("row",rangename)-1),0,4,50,1))

in the top example if a 2 is in a1 then it will average(average!e1:e50)
in the top example if a 2 is in a2 then it will average(average!e2:e51)

Hi skersch,
You cannot automatically disable the 'enable macros' pop-up. This is a security feature to protect users from malicious macros'. It would be like an internet download that automatically disabled your firewall or anti-virus.

It is up to individual users to select their security settings.
Under Tools-Macro-Security users may set their macro security to High, Medium or Low. High disables all macros, Medium gives the pop-up alert, and Low lets all macros run without the pop-up alert.

ooops, my bad.

="a"&MATCH(2,rangename,0)+CELL("row",rangename)-1

or

="a"&MATCH(2,Sheet2!A10:A100,0)+CELL("row",Sheet2!A10:A100)-1

Much prettier Richie.

John, welcome to the Forums.
Something like this?

Load UserForm1
UserForm1.TextBox1.Value = Range("a1").Value
UserForm1.TextBox2.Value = Range("a2").Value
UserForm1.Show

...and to save back to cells...
Range("a2").Value=UserForm1.TextBox2.Value
UserForm2.hide

I hadn't noticed that before. It seems to happen only when the zoom is reduced (ie 75%).
You can change font size by right clicking on the button when you are in design mode. This will pull up the Properties window that has a font attribute line. You could also try resizing button (bigger).

I'm sure this isn't the prettiest way to do it but it works. Is this what you are looking for?

Function Sumprodx(teamname As Range, TeamVal, outcome As Range, OutVal)
Sumprodx = 0
For x = 0 To teamname.Rows.Count - 1
a = Cells(teamname.Row + x, teamname.Column).Value
b = Cells(outcome.Row + x, outcome.Column).Value
Sumprodx = Sumprodx + (a = TeamVal) * (b = OutVal)
Next x
End Function

Sub Test3()
z = Sumprodx(Range("whatever1"), 1, Range("whatever2"), "w")
MsgBox z
End Sub

Here is one approach using =match instead of vlookup:

="a"&MATCH(2,a10:a100,0) or
="a"&MATCH(2,rangename,0)
where you are searching for "2" in range "rangename". This example ssumes that rangename in column A. 0 indicates that we are looking for an exact match.

Using sumproduct as a worksheet function in VBA only seems to work in its simplest form (the example below works) but not when it gets more complicated ie teamname=1 * outcome=w

answer = Application.WorksheetFunction.SumProduct(Range("teamname"), Range("outcome"))

The following quick & dirty code works also:

Sub test2()

TeamVal = 1
OutVal = "W"
y = 0
For x = 0 To Range("TeamName").Rows.Count - 1
a = Cells(Range("teamname").Row + x, Range("teamname").Column).Value
b = Cells(Range("outcome").Row + x, Range("outcome").Column).Value
y = y + (a = TeamVal) * (b = OutVal)
Next x

MsgBox y
End Sub

Hi Dennis, it's not on my WinNT machine at work, but you probably already knew that.

Hi scharles, welcome to the Forums.
The code below deletes all page breaks on the page then recreates them at each line in column A where it finds text "xxx". You can change "xxx", and the "xxx" can even be imbedden in a longer string ie " New Pagexxx number".

Hope this is what you are looking for. The other approach that I can think of offhand is more complicated and would be for the macro to loop & find the first line with "xxx", define the print area, print, find the next line with "xxx", redefine the area, print etc...loop, loop,loop...

Sub PageBreaker()
Cells.PageBreak = xlPageBreakNone
With Worksheets("sheet1").Range("a1:a500")
Set c = .Find("xxx", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Worksheets("Sheet1").Rows(c.Row).PageBreak = xlPageBreakManual
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub