excel - VBA Macro that finds correct place to copy paste into -

first of hello guys,

i´m working on vba script may see title. thing know basic java , things looked here , there make code running.

now thing want have 2 sheets synchronise.

to more clear, if write in sheet1 , activate macro gets copied correct fields in sheet2.

my current code looks , guess easiest way want do:

    sub magic()      dim sh1 worksheet     dim sh2 worksheet      set sh1 = activeworkbook.sheets("postenkosten")     set sh2 = activeworkbook.sheets("monatskosten")      dim pa integer     dim pb integer     dim ma integer     dim mb integer  // go through designated columns , rows pa = 4 34 step 3  pb = 6 10 step 1     // check if empty     if sh1.cells(pb, pa).value <> ""           //if not got sheet2 , designated cells there         ma = 1 30 step 3              mb = 1 12 step 1                //here comes critical part - if cell sheet 1 same headline (cell) in sheet 2 then...                 //if not next headline , compare                if sh1.cells(pb, pa) = sh2.cells(ma, 2)                  //make sure have empty row don't override things , copy cells adjacent sheet 2                   if sh2.cells(mb, ma) = ""                  else                        sh1.cells(4, pa).value.copy sh2.cells(mb, ma)                         sh1.cells(pb + 1, pa).value.copy sh2.cells(mb + 1, ma)                        sh1.cells(pb + 2, pa).value.copy sh2.cells(mb + 2, ma)                  end if                 end if              next mb         next ma     end if next pb     next pa      end sub        //go , next cell in sheet 1 

i hope mean. if have ideas how fix code happy (i spent @ least week make work)

to further visualise problem

sheet1 sheet2

thanks lot reading , trying help.

if need more information don't hesitate ask provide quick possible :)

edited after op's request (see lines '<=== edited comment)

maybe need follows

sub magic()  dim sh1 worksheet dim sh2 worksheet dim postendates range, monatdates range, cell range, fndrng range  set sh1 = activeworkbook.worksheets("postenkosten") set sh2 = activeworkbook.worksheets("monatskosten")  set postendates = setdatesrange(sh1.range("d6:d24"), 1, 10, 1, 3) '<== set base range , "multiplying" factors per needs set monatdates = setdatesrange(sh2.range("a2:aj2"), 3, 1, 18, 1) '<== set base range , "multiplying" factors per needs  each cell in postendates     set fndrng = finddate(cell, monatdates)     if not fndrng nothing         if isempty(fndrng.offset(13))               '<=== edited             fndrng.end(xldown)                      '<=== edited                 sh1.cells(4, cell.column).copy           '<=== edited                 .offset(1).pastespecial xlpastevalues    '<=== edited                 cell.offset(, 1).resize(, 2).copy        '<=== edited                 .offset(1, 1).pastespecial xlpastevalues '<=== edited             end                                     '<=== edited         end if     end if next cell  end sub   function finddate(rngtofind range, rngtoscan range) range dim cell range  each cell in rngtoscan     if cell = rngtofind         set finddate = cell         exit     end if next cell  end function   function setdatesrange(inirng range, nrowssteps long, ncolssteps long, rowstep long, colstep long) range dim unionrng range dim long, j long  set unionrng = inirng inirng     = 1 nrowssteps         j = 1 ncolssteps             set unionrng = union(unionrng, .offset((i - 1) * rowstep, (j - 1) * colstep))         next j     next end  set setdatesrange = unionrng.specialcells(xlcelltypeconstants) end function