# Posts by tinyjack

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

## Files

• Movelines.zip

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

TJ

Re: a vba macro code to delete certain worksheets

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

Code
1. Dim wsItem As Worksheet
2. For Each wsItem In ThisWorkbook.Worksheets
3. 'Change delete to whatever you need
4. 'DisplayAlerts can be used to avoid dialog box being displayed
5. If wsItem.Name Like "delete*" Then wsItem.Delete
6. Next

HTH

TJ

Re: Calling Multiple Macros

Just wrap them in a sub, so

Code
1. Sub RunAll()
2. Sub1
3. Sub2
4. 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
End If
Next wSht
End With

End Sub
[/vba]

TJ

Re: Shorten code

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

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!

Code
1. If c Mod 2 <> 1 Then TargetSheet.Cells(c.Row, 8) = c.Value
2. 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