How can I simplify or loop excel vba code for data exchange in different sheets?

Question

I'm making calculation form where you can enter element quantity and select materials from drop down list on sheet1 (all info will go to the sheet2) and in sheet2 you can change same materials that were set in sheet1 to other from drop down list and it automatically update info in sheet1. I have the code that works perfectly(see code below) and it works both ways. Question is that the code for checking and changing the values is ok with me but it has to to be repeated for about 10 different positions(ranges) that are defined next to 51 different element calculations and it need to be entered 1000+ times. If there is possibility to somehow group that 10 position with one code similar to "loop" then it would be repeated just for 100+ times :)

the idea is to be able to adjust "materials"in both sheets see picture enter image description here

Main code is in sheet and yes it is _change

calculation sheet (sheet1)

Private Sub Worksheet_Change(ByVal Target As Range)
Call FloorElementExchange1
End Sub

main sheet(sheet2)

Private Sub Worksheet_Change(ByVal Target As Range)
Call FloorElementExchange2
End Sub

and all another code that actually do the check and replacement of value is in module (sub FloorElementExchange1()) that goes like this:

sub FloorElementExchange1()

    If Worksheets("CALCULATION").Range("N67") <> Worksheets("main sheet").Range("F44") Then
        Worksheets("main sheet").Range("F44").Value = Worksheets("CALCULATION").Range("N67").Value
    End If

sub FloorElementExchange2()

    If Worksheets("main sheet").Range("F44") <> Worksheets("CALCULATION").Range("N67") Then
        Worksheets("CALCULATION").Range("N67").Value = Worksheets("main sheet").Range("F44").Value
    End If

If A <> B Then A = B: Easier to just go A=B and forget the check, because that is what you end up with anyway. — AJD
@TimWilliams I'm not sure what the name for this code is. I found it online, checked it and since it worked for what I was seeking I started to use it. But because I am new to all VBA coding I try not to mess a lot with the codes. — Andrius Ramaškevičius
@AJD if I remove the check part from both sheets then for some reasons it kills excel. If I write the same code but only for one sheet then its ok. I'm not sure is that somehow correspond with code check line but it seems to me that it does. — Andrius Ramaškevičius

Dorian Dorian · Accepted Answer · 2019-07-26T08:21:59

give this a try , your If condition isn't needed here

Sheet1 :

Worksheets("main sheet").Range("F15").Value = Worksheets("CALCULATION").Range("N17").Value

Sheet2 :

Worksheets("CALCULATION").Range("N17").Value = Worksheets("main sheet").Range("F15").Value

Note : Your check is useless and waste time .. Try without this will be quite faster

How can I simplify or loop excel vba code for data exchange in different sheets?

1 Answers