# Posts by tinyjack

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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

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
``````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

Code
``````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
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
``````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