VisualBasic - Change vertical to horizontal Data

garwain

New Member
Joined
Oct 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,

i´ve got a problem (sorry for my bad English ?)

There is an “input”-mask and a “list”. Data is entered manually into the mask, which is then copied into the list by makro. There are several Areas that are filled out in the “input”-mask and then output in the list in one line, one after the other.

The problem is: I changed an area in the mask so that it is easier to enter data.
Instead of entering the data vertically, they are now displayed horizontally in one line.
In the Makro there is the command: "Application.Transpose", which outputs the data that are entered vertically horizontally and vice versa.
Therefore, the third input area, which is already horizontal, is unfortunately entered vertically in the list.
I've tried a lot on the line, but I can't get this command out without an error message.

So I need a change in the code for "Daten3"

Hope u understand what I mean. Have a look in the code and the example file ?


My next Problem:
After copying, I want the value from column A of the "List", which is in the row of the last record, to be copied into the first sheet "Inputs" in cell B10.
Example: I have already transmitted 5x data, i.e. 5 filled lines in the list. The next entry is made in line 7, where column A contains the number "RE0005".
This number should now be copied directly after the copying process, with the same macro (i.e. without having used another button, etc.) in "Inputs" in field B10.
In other words: The code should do the following: "Find the last entry in" List "column B and copy the cell to the left of it (i.e. from column A) and paste it into" Input "cell B10"
Since I only have a rudimentary knowledge of VBA, it would be nice if someone could directly tinker with the line of code that I just have to insert.

Thanks in advance

VBA Code:
Sub transfer_werte()

    Dim rngDaten1    As Excel.Range
    Dim rngDaten2    As Excel.Range
    Dim rngDaten32    As Excel.Range
    
    Set rngDaten1 = Worksheets("Eingaben").Range("B4:B9")
    Set rngDaten2 = Worksheets("Eingaben").Range("E4:E6")
    Set rngDaten3 = Worksheets("Eingaben").Range("A13:C13")
    
    With Worksheets("Liste")
          With .Cells(.Rows.Count, 2).End(xlUp)
               .Offset(1, 0).Resize(rngDaten1.Columns.Count, rngDaten1.Rows.Count).Value = Application.Transpose(rngDaten1.Value)
        End With
    End With
          
    With Worksheets("Liste")
          With .Cells(.Rows.Count, 8).End(xlUp)
               .Offset(1, 0).Resize(rngDaten2.Columns.Count, rngDaten2.Rows.Count).Value = Application.Transpose(rngDaten2.Value)
        End With
    End With

    With Worksheets("Liste")
          With .Cells(.Rows.Count, 11).End(xlUp)
               .Offset(1, 0).Resize(rngDaten3.Columns.Count, rngDaten3.Rows.Count).Value = Application.Transpose(rngDaten3.Value)
        End With
    End With


End Sub
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
Welcome to MrExcel!
See if this works for you ...

VBA Code:
Sub transfer_werte()

    Dim shtEingaben  As Excel.Worksheet
    Dim rngDaten1    As Excel.Range
    Dim rngDaten2    As Excel.Range
    Dim rngDaten3    As Excel.Range

    Set shtEingaben = ThisWorkbook.Worksheets("Eingaben")
    Set rngDaten1 = shtEingaben.Range("B4:B9")
    Set rngDaten2 = shtEingaben.Range("E4:E6")
    Set rngDaten3 = shtEingaben.Range("A13:C13")

    With Worksheets("Liste")
        With .Cells(.Rows.Count, "B").End(xlUp)
            .Offset(1, 0).Resize(rngDaten1.Columns.Count, rngDaten1.Rows.Count).Value = Application.Transpose(rngDaten1.Value)
            .Offset(1, 6).Resize(rngDaten2.Columns.Count, rngDaten2.Rows.Count).Value = Application.Transpose(rngDaten2.Value)
            .Offset(1, 9).Resize(rngDaten3.Rows.Count, rngDaten3.Columns.Count).Value = rngDaten3.Value
            shtEingaben.Range("B10").Value = .Offset(1, -1).Value
        End With
    End With
End Sub
 

garwain

New Member
Joined
Oct 1, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
THANK YOU VERY MUCH !!!!
YOU ARE MY HERO !!!

For 2 weeks I've been trying to solve the problem and in German forums you only get stupid answers, insults or very complex, cumbersome and illogical codes that don't work: D
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for the feedback :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,133
Messages
5,835,594
Members
430,369
Latest member
pingel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top