select and check multiple cells

[lugwisha]

hi, I'm new to Excel VBA. I am trying to write code that will select and check multiple cells. if cells contain a specific integer, then an assigned calculation would be done for the adjacent cells.
I've pasted below my latest attempt to archive the above-mentioned objective

Sub Rebar_total_length ()

Dim total As Integer

Dim A As Integer

Dim b As Integer

Dim c As Integer

Dim d As Integer

Dim e As Integer


total = Range("A5:a80")

If total = 38 Then

Range("g5:g80") = A + b + c

End If

End Sub

 

[Joe4]

Welcome to the Board!

Your code really does not make any sense. You have not set A, B, and C equal to anything.
Also, if you want to add the the values in a range, you need to use addition (+) or SUM. This will NOT sum up the values in a range:

total = Range("A5:a80")

Let's forget about the code for a minute. Can you just please post a small sample of data, and explain exactly what you want to happen?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[lugwisha]

thanks for reply and pardon me for the late response. I'm basically trying a set a specific calculation on multiple cells by checking if said cells contain a specific number, if yes, then a specific calculation will be assigned, if no then another calculation will be assigned and so on .ill now try and give an example.

If cell b1 is equal to 38 then a1 is equal to (cell c1 + cell d1 + cell e1) or if cell b1 is equal to 60 then cell a1 is equal to ((2 x cell c1) + (2 x cell d1) + 140) and so on for 40 other conditions. I have figured out how to do this for one cell (a1) but haven’t figured out how to repeat this function for all the cells below (a2 to a50).

I really hope I was able to explain, please find attached a data sample.

 

[Joe4]

You have 40 different conditions/formulas? Wow! That is going to be some long code!
I would recommend using a CASE statement, where you can list each value, and the formula for each (see: MS Excel: How to use the CASE Statement (VBA))

I would loop through the range of cells you want to apply this to, and make use of the OFFSET function, which allows you to check other columns of whatever row you are currently on (see: VBA Excel Range Cells and Offset)

Here is what that code might look like for your first two conditions.
You can keep adding more "Case" lines for your other conditions under the first two I already listed based upon your explanation.

Sub Rebar_total_length()

    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Loop through cells B1:B50
    For Each cell In Range("B1:B50")
'       Check value in column B
        Select Case cell.Value
'           Based on value in column B, calculate value and put in column A
            Case 38
                cell.Offset(0, -1).Value = cell.Offset(0, 1) + cell.Offset(0, 2) + cell.Offset(0, 3)
            Case 60
                cell.Offset(0, -1).Value = (cell.Offset(0, 1) * 2) + (cell.Offset(0, 2) * 2)
        End Select
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[lugwisha]

Thanks a lot, I copied the code and made some tweaks and it worked like a charm.
got to understand the offset function in detail and other conditional statements.
I really appreciate the help.

 

[Joe4]

You are welcome.
Glad I was able to help!