Trying to set value of a range equal to IF Statement

[ohFice]

Hello,

So I made a column, and placed some formulas in there to provide a number based on other cells.

Within VBA I am trying to set a different range equal to a specific percentage. The percentage would be either 25%, 50% or 75% depending on whether the corresponding amounts in the first range (if larger than 3, 25%, larger than 5, 50%, and larger than 10, is 75%); however, I am currently stuck, and not sure what I am doing wrong. Please see code below.

[FONT=Verdana]'**Sets Variable For Current Workbook
Dim WorkBook1 As Workbook
    Set WorkBook1 = ThisWorkbook

[/FONT]
[FONT=Verdana]'**Sets Variable For Rent_Roll Sheet
Dim Worksheet1 As Worksheet
    Set Worksheet1 = WorkBook1.Worksheets("Sheet 1")

[/FONT]
[FONT=Verdana]'**Sets Variables For Range to include Percentages
Dim OfficeSC As Range
Dim OfficeRange As Range
    Set OfficeSC = Worksheet1.Range("Q16")
    Set OfficeRange = Range(OfficeSC, OfficeSC.End(xlDown))
    
 '**Sets Variables For Years Column
Dim OfficeYears As Variant
    Set OfficeYears = OfficeRange.Offset(0, 4)
       
'''Runs IF and sets percentages based on years.
If OfficeYears > 10 Then
    OY = 75
ElseIf OfficeYears > 5 Then
    OfficeRange = 50
ElseIf OfficeYears > 3 Then
    OfficeRange = 25
End If[/FONT]

I feel like I'm using the wrong variable type for OfficeYears, but not sure.

 

[MoshiM]

Where are you stuck?

 

[ohFice]

Hi Moshi,

Well basically this entire section doesn't work.

[COLOR=#333333][FONT=Verdana] '**Sets Variables For Years Column[/FONT][/COLOR][FONT=Verdana]Dim OfficeYears As Variant
    Set OfficeYears = OfficeRange.Offset(0, 4)
       
'''Runs IF and sets percentages based on years.
If OfficeYears > 10 Then
    OY = 75
ElseIf OfficeYears > 5 Then
    OfficeRange = 50
ElseIf OfficeYears > 3 Then
    OfficeRange = 25 [/FONT][COLOR=#333333][FONT=Verdana]End If[/FONT][/COLOR]

I changed the variable type to variant just randomly trying things, but that obviously didn't work. Any idea what I'm doing wrong?

 

[MoshiM]

Hi Moshi,

Well basically this entire section doesn't work.

Does

Set OfficeRange = Range(OfficeSC, OfficeSC.End(xlDown))

return a range or a cell?

And have you defined what OY is

 

[DanteAmor]

You can put a sample of your data.
Do you want to update a cell in the sheet with the result of the If statement?

 

[ohFice]

Moshi,
I am trying to return a range, and the OY was an error it should be OfficeRange like the variable noted in the lines below it; however, it does not work with that variable either.

DanteAmor,
I am trying to update a range of cells with the result of the IF Statement.

This is so frustrating Apologize for the late response, I have been very busy!

Thanks again for the help!

-Michael

 

[MoshiM]

Moshi,
I am trying to return a range, and the OY was an error it should be OfficeRange like the variable noted in the lines below it; however, it does not work with that variable either.

DanteAmor,
I am trying to update a range of cells with the result of the IF Statement.

This is so frustrating Apologize for the late response, I have been very busy!

Thanks again for the help!

-Michael

In your if statement you are testing if a range is bigger than something. What are testing for exactly? The number of cells or the value of each cell within that range?

The following tests each cell's value within the range and updates the stuff you have in OfficeRange. I don't know how many cells you'll have to test so it may be better to use an array instead.

Dim WorkBook1 As Workbook, Worksheet1 As Worksheet, OfficeSC As Range, OfficeRange As Range, OfficeYears As Range, C As Range

    Application.screenupdating=false

    Set WorkBook1 = ThisWorkbook

    Set Worksheet1 = WorkBook1.Worksheets("Sheet 1")

'**Sets Variables For Range to include Percentages

    Set OfficeSC = Worksheet1.Range("Q16")
    
    Set OfficeRange = Range(OfficeSC, OfficeSC.End(xlDown))
    
 '**Sets Variables For Years Column
    Set OfficeYears = OfficeRange.Offset(0, 4)
       
'''Runs IF and sets percentages based on years.
For Each C In OfficeYears

    If C > 10 Then
    
        C.Offset(0, -4) = 75
        
    ElseIf C > 5 Then
    
        C.Offset(0, -4) = 50
        
    ElseIf C > 3 Then
    
        C.Offset(0, -4) = 25
        
    End If

Next C
application.screenupdating=true

 

[ohFice]

I am checking the amount of years something is; if greater than 10; then assume 75%, and so on.

I am trying to test the VALUE within that range; sounds like I'm doing that incorrectly though. Can you advise further? does the solution you provided test for value or count?

The range shouldn't be that large, and I am such a beginner that I am unfamiliar with arrays.

 

[MoshiM]

I am checking the amount of years something is; if greater than 10; then assume 75%, and so on.

I am trying to test the VALUE within that range; sounds like I'm doing that incorrectly though. Can you advise further? does the solution you provided test for value or count?

The range shouldn't be that large, and I am such a beginner that I am unfamiliar with arrays.

It tests the value of the original cell offset by 4 columns and if it meets the criteria then the original cell is updated.

 

[ohFice]

It works!! Thanks so much man, very much appreciated, you're a wizard!

Would you happen to know how to carry an in-place formula over automatically into a cell that was just inserted/created? That probably sounds confusing; basically when I create a new row it is just completely blank, but I want the formulas that are in the cells around it to carry over into the new row.