Macro that compares value and returns the difference between them

Asked

Viewed 53 times

0

I have a spreadsheet Home and another BD, in BD I have data of various types of cars (Name, Type (Sports, Casual), Color, Seats(2 or 5) and Price. And in Home I have 1 group of Buttons option, with the two seat options. What I need to do is take the Seat options that the user chooses, and show in my spreadsheet start the data and the price difference between the registered cars that have the type of seat chosen, but I have to compare the red sports cars only with the red casual cars and the blue sports cars with the blue casual ones and so on. The idea is to show the price difference between sports cars and casual cars with 2 seats, or with 5 seats, the seat option depends on the user choose. An example of what I want to appear if the user chooses two seats looks like this: Casual Black with two seats -23% compared to sporty Black with two seats. Red casual with two seats -43% compared to red sports with two seats. ...

Below I leave the code I wrote so far:

Sub Compara()
Dim Tipo
Dim Cor
Dim Assento
Dim Preco1
Dim Preco2
Dim Nome
Dim Resultado
Dim BotAssento (A duas dimensão Bot eu criei pra guardar o selecionado pelo usuario no botão de opção)
Dim Celula As Object

Tipo = Worksheets("BD").Range("B4", "B41")
Cor = Worksheets("BD").Range("C4", "C41")
Assento = Worksheets("BD").Range("D4", "D41")
Preco = Worksheets("BD").Range("F4", "F41")
Nome = Worksheets("BD").Range("A4", "A41")


For Each Celula In Worksheets("Inicio").Range("A:A")

If Celula = "" Then

Celula.Offset(0, 0) = Resultado

If BotAssento = "2" Then


End If

Exit Sub


End Sub

I know what I want to do after this if I stopped but I’m not able to put in practice, someone can help me?

excel vba excel-vba macro

1 answer

0

I left explained every detail in the code, see if it meets
Download Sheet Example

Public Sub CompararCarros()
Dim Tipo        As String 
Dim Cor         As String
Dim assento As Integer
Dim ultimaLin, i, j, k          As Long


ultimaLin = Sheets("BD").Range("A1048576").End(xlUp).Row ' Pega a ultima linha da coluna A
'i = 2  para inicio dos dados
assentos = Sheets("Inicio").Range("E2") ' pego o número de assentos na Aba Inicio
k = 5
Sheets("Inicio").Range("A5:E500").ClearContents 'apago o anterior antes de começar
For i = 3 To ultimaLin ' percorro todas as linhas da aba BD

If Sheets("BD").Cells(i, 4) = assentos Then ' verifico se o carro é do mesmo assento solicitado
    Cor = Sheets("BD").Cells(i, 3) ' pego a cor do carro
    Tipo = Sheets("BD").Cells(i, 2) ' pego o tipo do carro (Casual, Esportivo)
    For j = (i + 1) To ultimaLin ' percorro todas as linhas do BD a partir da linha encontrada + 1
        If Sheets("BD").Cells(j, 2) <> Tipo And Sheets("BD").Cells(j, 3) = Cor And Sheets("BD").Cells(j, 4) = assentos Then ' verifico se o carro é do tipo diferente e o restante atende (Cor e assento)
            Sheets("Inicio").Cells(k, 1) = Sheets("BD").Cells(i, 1) ' passo os dados para o inicio
            Sheets("Inicio").Cells(k, 2) = "x"
            Sheets("Inicio").Cells(k, 3) = Sheets("BD").Cells(j, 1)
            Sheets("Inicio").Cells(k, 4) = Sheets("BD").Cells(i, 5) / 
            Sheets("BD").Cells(j, 5)
            k = k + 1 ' pulo uma linha quando concluido
        End If
    Next
   End If
Next
End Sub

Browser other questions tagged excel vba excel-vba macro

You are not signed in. Login or sign up in order to post.