Posts by Smallman

Re: Combining Multiple IF AND and OR statements

It is all about how you set your refererence table up.

[TABLE="width: 154"]

Data

Return

- 50,000

[TD="align: right"]8
[/TD]

50,000

[TD="align: right"]13
[/TD]

100,000

[TD="align: right"]20
[/TD]

500,000

[TD="align: right"]40
[/TD]

1,000,000

[TD="align: right"]100
[/TD]

[/TABLE]

If you set the table like the above then put your value in D2 the following.

=VLOOKUP(D2,A2:B6,2)

This will give you the correct answer.

Take care

Smallman

Re: Extract from Cells based on another cell

No problems at all.

Re: VBA Code to Create an Array

Hi

Welcome to the forum. Members usually expand on their question a little more

The array can be either directly entered in VBA or you may reference cells which makes the array more dynamic. Depending on which method you use will determine how you treat the array.

I have written a little about them on this blog. It stars slow and takes you through some of the array types.

Take care

Smallman

Re: Extract from Cells based on another cell

Hi

I am not entirely sure if this is what you are after as it was a curious example (all the proxies were the same) however it will give you a starting point.

Sub SimpleLoop()
Dim i As Integer


    For i = 2 To 31
        Cells(2, i) = Cells(Cells(1, i).Value, 32)
    Next i
End Sub

Take care

Smallman

Re: Depreciation

Hey Matank

I don't think you have quite grasped the concept of depreciation. You can depreciate an asset over a number of years and the maximum you can depreciate the asset is 100% of its purchase value. Your table has your depreciation at 220% which is creative accounting at its finest.

Can you please clarify this for us?

Thanks

Smallman

Re: Delete Row Macro Is Taking Forever

It does not sound right to me. Are you using the whole of the column in the formulation? The file only has 20K rows and it is not too broad I suspect the culprit is some agricultural formula design. If you upload a file or cut down version of it, we can spreadsheet doctor it for you.

Take it easy

Smallman

Re: Delete Row Macro Is Taking Forever

Maybe something like the following.

Sub DeleteRowsIfBlank1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("AZ1", Range("AZ" & Rows.Count).End(xlUp)).AutoFilter 1, ""
Range("AZ2", Range("AZ" & Rows.Count).End(xlUp)).EntireRow.Delete
Range("AZ1").AutoFilter
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Take care

Smallman

Re: Consolidate Similar but Often Updated Worksheet Data onto one Worksheet

Thanks for your kind words. You too!!! See you next time.

Re: Consolidate Similar but Often Updated Worksheet Data onto one Worksheet

Hi

Thanks - yes - just change A2 to A3. I will assume all of your data is the same on each sheet starting in the same place each sheet.

Take care

Smallman

Re: Consolidate Similar but Often Updated Worksheet Data onto one Worksheet

Hi Daniel

Give the following a go:

Private Sub Worksheet_Activate()
Dim sh As Worksheet
Application.ScreenUpdating = False
[a2:S10000].Clear
    For Each sh In Sheets
        If sh.Name <> "Main" Then
            sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp)).Resize(, 19).Copy Range("A" & Rows.Count).End(xlUp)(2)
        End If
    Next sh
    Application.ScreenUpdating = True
End Sub

File attached to show workings.

Take care

Smallman

Re: Sum different sets of data in the same column

Hi

You can't do this easily. If someone provides you with a formulation it will be complex. What would be better would be if you formatted your raw data so you had rows filled the Chess (W), QAD (W) etc.

Take care

Smallman

Re: Change system separator

Reading between the lines perhaps you just want to streamline the vertical axis in your chart. Just changing the format of the source data will do that. This is the code but I would do this manually.

Sub Testo()
    [b3:C5].NumberFormat = "0.0"
End Sub

Take care

Smallman

Re: Excel Countifs

Hi Chhiew

The following formula in E2 should sort things.

=IF(C2=D2,"no Change",IF(D2<C2,"Down","Up"))

Take care

Smallman

Re: Finding number values between and replacing with another number value

Hi

Here is a formula solution. Write the formula, then overwrite the values or have it in a new column so you can see the original. The method requires a data table and the following formula.

=VLOOKUP(D2,$A$2:$B$5,2,1)

File attached to show workings.

Take care

Smallman

Edit - I saw the Jamad's response after I hit post.

Re: Show Planned and Actual Items in Pivot Table

Hi Chullan88

Welcome to Ozgrid.

It is all about how your source data is set up. If you set it up slightly differently , took about 30 seconds to change it. You get what you are looking for. File attached to show workings.

Take care

Smallman

Re: VBA to calculate the number of days in a month based on ActiveSheet name

Hi

This should work for you. Works on the activesheet

Option Explicit
Option Compare Text
Sub DaysMth()
Dim idays As Integer
    idays = Day(DateSerial(Year(Now), Month(DateValue("01 " & ActiveSheet.Name & " " & Year(Now))) + 1, 1) - 1)
    MsgBox idays
End Sub

Take care

Smallman

Re: calculate points a player in a basketball team made.

Is there any reason you don't simply put a unique list of players in Column E and let formula solve the problem for you?

Take care

Smallman

Re: calculate points a player in a basketball team made.

Slight adjustment to Mike's formula as E1 is the header in the sample file.

=SUMIF(A:A, E2,B:B )

Always a good idea to post a sample with your question. Takes guess work out.
Take care

Smallman

Re: How do I have cells with colors based on their respective values?

Hi NearEdge

Welcome to Ozgrid

Is this the sort of effect you are looking for. Done with data bars in conditional formatting.

Take care

Smallman

Re: calculate points a player in a basketball team made.

Hi

Welcome to Ozgrid. Is this the sort of thing you were looking for?

Take care

Smallman