Posts by tinyjack

Re: Area Of A Polygon

This thread might be of interest to you. The final code I posted works (I think), but I am sure there are better ways of doing it.

http://www.xtremevbtalk.com/showthread.php?p=626774

TJ

Re: Abs Function Nested In Sumif Equation?

Have a look at SUMPRODUCT:

=SUMPRODUCT((A1:M1="p")*ABS(A8:M8))

HTH

TJ

Re: Calculate Single Digits in Long Number

Try:

=IF(LEN(B2)<3,9999,(MOD(RIGHT(B2)-10,-10)+10)*5+(MOD(LEFT(RIGHT(B2,2))-10,-10)+10)*2.5+(MOD(LEFT(RIGHT(B2,3))-10,-10)+10))

TJ

Re: If Then Else Elseif Comparing Text Numbers

You could use something like:

=A1*10^(4-INT(LOG(A1)))

To make them the same lenght and then compare.

TJ

Re: Selecting Lines In Vb

Here is my test file.

TJ

Re: Selecting Lines In Vb

Works for me.

You "Basically the code is", could you the actual code?

TJ

Re: Skip Two Rows In Formula

Not quite sure if this is what you mean, but if you copy it down once then highlight the first 2 plus 2 blank rows you can then drag down.

ie
A1 1
A2
A3
A4 2

then highlight A1:A6 and drag down.

TJ

Edit: ignore this post as there is no need to do the first copy

Re: Chart Building With Ranked Data

This might help you.

TJ

Re: a vba macro code to delete certain worksheets

You could use a For Each ... Next loop:

Dim wsItem As Worksheet


For Each wsItem In ThisWorkbook.Worksheets
    'Change delete to whatever you need
    'DisplayAlerts can be used to avoid dialog box being displayed
    If wsItem.Name Like "delete*" Then wsItem.Delete
Next

HTH

TJ

Re: Calling Multiple Macros

Just wrap them in a sub, so

Sub RunAll()
   Sub1
   Sub2
End Sub

then attach that to the button.

TJ

Re: Counting The Occurences Of A Number And Returning The Max Value

Any reason why you cannot just use =MODE() on the sheet instead of using VBA?

TJ

Re: Explanation of Resize Method

The Values or XValues returns a Variant Array, not a Range the Resize requires.

Without knowing exactly what you are trying to achieve, I would suggest you read the following about dynamic ranges.

http://www.ozgrid.com/Excel/DynamicRanges.htm

HTH

TJ

Re: For/Next Coding

Have a look at Arrays rather than using multiple variables

[vba]
Dim JD(1 to 3) As String
Dim i As Long

JD(1) = "Sheet1"
JD(2) = "Sheet2"
JD(3) = "Sheet3"

For i = 1 to 3
Worksheets(JD(i)).Activate
MsgBox "Sheet " & JD(i) & " open"
Next i
[/vba]

HTH

TJ

Re: Shorten code

or to mod Roy's post:

[vba]
Private Sub UserForm_Initialize()
Dim wSht As Worksheet

With Me.ComboBox1
For Each wSht In ActiveWorkbook.Worksheets
If wSht.Name Like "Week*" Then
.AddItem wSht.Name
End If
Next wSht
End With

End Sub
[/vba]

TJ

Re: Shorten code

What about:

[vba]
ComboBox1.AddItem 0
For x = 1 To 34 Step 1
ComboBox1.AddItem Format(x, "0")
Next x
returnvalue = ComboBox1
If returnvalue = 0 Then
Sheet79.Select
Else
Sheets("Week " & returnvalue).Visible = True
End If
[/vba]

TJ

Re: specify a range

For Info:

http://www.visualbasicforum.com/showthread.php?t=219963

TJ

Re: validation and the end of the list

Have a look at Dynamic Ranges:

http://www.ozgrid.com/Excel/DynamicRanges.htm

TJ

Re: Multiplying a Text Value

You could use this:

=TRIM(REPT(A1&" ",B1))

HTH

TJ

Re: using a loop to update a range

It is 2 lines of code as in the original post!

If c Mod 2 <> 1 Then TargetSheet.Cells(c.Row, 8) = c.Value
Next c

TJ

Re: Proportions

You could use this in K2:

=L2/SUM(OFFSET(L$2:L$5,INT((ROW()-2)/4)*4,0))

HTH

TJ