How Can You Fix PC Dll Errors Effectively?: How Can I Fix

Batch transpose macro

Please help, I am a newbie and struggling with this one.
Ive seen other answers close to this but they do not exactly answer.

Every month I import a data file into excel which is basically an online form which populates column A of the excel sheet.
It contains fieldc such as name, email, date, good experience etc.
I have approx 100 of these forms which I need to batch transpose
so the data appears transposed and runs from column B through to maybe AA (can happen on another tab if necessary).

This is how the format of the form looks, there is a break after each one so I showed 2 as an example;

Body

Date of experience : 06/13/2012

Ease of reservation : Excellent

Ease of reservation comments : Comments

Check in experience : Above average

Check in experience comments : Comments

Room number : 212

Cleanliness of bedroom : Above average

Cleanliness of bedroom comments : Comments

Standard of bedroom dicor : Below average

Standard of bedroom dicor comments : Comments

Cleanliness and condition of public areas : Above average

Cleanliness of public areas comments : Comments

Check out experience : Excellent

Check out experience comments : Comments

Ease of restaurant reservation : Please select

Ease of rest res comments : Comments

Welcome : Please select

Welcome comments : Comments

Breakfast experience : Please select

Breakfast Experience Comments : Comments

Lunch / dinner experience : Please select

Lunch/dinner experience comments : Comments

Quality of food : Please select

Quality of food comments : Comments

Friendliness of team in restaurant : Please select

Friendliness of team in rest comments : Comments

Billing experience : Please select

Billing experience comments : Comments

In order to keep up to date with our special offers and news please enter your details below and we will send you our regular newsletter : True

E mail address :

-----

No virus found in this message.

Checked by AVG - www.avg.com

Version: 2012.0.2180 / Virus Database: 2437/5105 - Release Date: 07/01/12

Date of experience : 06/27/2012

Ease of reservation : Above average

Ease of reservation comments : Comments

Check in experience : Above average

Check in experience comments : Comments

Room number : 302

Cleanliness of bedroom : Excellent

Cleanliness of bedroom comments : Comments

Standard of bedroom dicor : Above average

Standard of bedroom dicor comments : Comments

Cleanliness and condition of public areas : Above average

Cleanliness of public areas comments : Comments

Check out experience : Above average

Check out experience comments : Comments

Ease of restaurant reservation : Above average

Ease of rest res comments : Comments

Welcome : Above average

Welcome comments : Comments

Breakfast experience : Not applicable

Breakfast Experience Comments : Comments

Lunch / dinner experience : Above average

Lunch/dinner experience comments : Comments

Quality of food : Above average

Quality of food comments : Comments

Friendliness of team in restaurant : Excellent

Friendliness of team in rest comments : Comments

Billing experience : Above average

Billing experience comments : Comments

E mail address :

I would like the Macro to select all of the data in column A and transpose it across rows so it looks something like this;

Ease of Reservation Ease of reservation comments Check in experience etc
Ease of Reservation Ease of reservation comments Check in experience etc
Ease of Reservation Ease of reservation comments Check in experience etc
Ease of Reservation Ease of reservation comments Check in experience etc

That way I can then search and replace the comments to change them into values to average the data.

I keep getting stuck on the Macro/VB code, please help I would be very grateful! Thank you so much Charly

This

Anwsers to the Problem Batch transpose macro

Download SmartPCFixer for Free Now

In that case, why did it stop after only 3 blocks? I think I might have an idea.
Change this line in the code:
    While Range("C" & lnFirstRow).Value <> ""
to this:
    While Range("B" & lnFirstRow).Value <> ""
Now the code will check if the first cell in the block in column B is empty to determine when to finish working down column C.
If I'm right, in your fourth block, there was no "Date of experience" entered???
If that doesn't work, then yes, I need to see more of your data to work out what is happening.
Cheers
Rich

Hi Rich,

Hope you don't mind,I played around with your code.
Originally it was getting the calculation for lnGap wrong so when calculating the start/end row for subsequent blocks of data it was one row out each time and this was cumulative.
This now works for me.

Sub TransposeBlocks()
     Dim i As Long, lnFirstRow As Long, lnLastRow As Long, lnGap As Long, lnLen As Long

    Columns("A:A").Replace What:=" : ", Replacement:=":", _
         LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
         SearchFormat:=False, ReplaceFormat:=False

    Columns("A:A").TextToColumns _
         Destination:=Range("B1"), _
         DataType:=xlDelimited, _
         TextQualifier:=xlDoubleQuote, _
         ConsecutiveDelimiter:=False, _
         Tab:=False, _
         Semicolon:=False, _
         Comma:=False, _
         Space:=False, _
         Other:=True, _
         OtherChar:=":", _
         FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
         TrailingMinusNumbers:=True

    Columns("D:E").ClearContents

    For i = 1 To 100
         If Trim(Cells(i, "B").Value) = "Date of experience" Then
             lnFirstRow = i
         ElseIf Trim(Cells(i, "B").Value) = "E mail address" Then
             lnLastRow = i
             Exit For
         End If
     Next i
     For i = lnLastRow + 1 To lnLastRow + 100
         If Trim(Cells(i, "B").Value) = "Date of experience" Then
            lnGap = (i - lnLastRow) - 1
             'lnGap = i - lnLastRow
             Exit For
         End If
     Next i
     If lnFirstRow = 0 Or lnLastRow = 0 Or lnGap = 0 Then Stop
     lnLen = (lnLastRow - lnFirstRow) + 1

    Range(Range("B" & lnFirstRow), Range("B" & lnLastRow)).Copy
     Range("D1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
         SkipBlanks:=False, Transpose:=True
     Application.CutCopyMode = False

    i = 2 'destination row

    While Range("C" & lnFirstRow).Value <> ""
         Range(Range("C" & lnFirstRow), Range("C" & lnLastRow)).Copy
         Range("D" & i).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
             SkipBlanks:=False, Transpose:=True

        lnFirstRow = (lnLastRow + lnGap) + 1
         lnLastRow = (lnFirstRow + lnLen) - 1
         i = i + 1
     Wend

    Columns("B:C").Delete Shift:=xlToLeft
     Range("B1").Select
End Sub

Make Sure that your Hardware Meets the System Requirements

Microsoft Windows Requirements :

1 gigahertz (GHz) or faster 32-bit (x86) or 64-bit (x64) processor
1 gigabyte (GB) RAM (32-bit) or 2 GB RAM (64-bit)
16 GB available hard disk space (32-bit) or 20 GB (64-bit)

This error can be due to some configuration requirements for you system. If your computer hardware is not up to par, then make sure you update where necessary before continuing.

Another Safe way to Repair the Problem: Batch transpose macro:

How to Fix Batch transpose macro with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your computer. Open it, and it will perform a scan for your computer. The errors will be shown in the scan result.