+ Reply to Thread
Results 1 to 5 of 5

Excel Macro for transposing data based on a coloumn

  1. 06-20-2014, 02:22 PM #1
    excelNoobN is offline
    Registered User
    Join Date
    06-20-2014
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    2

    Exclamation Excel Macro for transposing data based on a coloumn

    I am new to excel vb scripting. I am trying to copy values from a number of rows of an excel sheet, depending upon a value (prpId) in another column. There are multiple 'prpId' and for each of these there are multiple values in another column, which too I need to check and if the second condition also matches, copy whatever value is present in the adjacent column of the same row to another cell.

    The code I wrote is:

    Please Login or Register  to view this content.
    When I run this I am getting a run time error. Can someone please help.
    Last edited by JBeaucaire; 06-20-2014 at 02:24 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.
    Reply With QuoteRegister To Reply
  2. 06-20-2014, 02:26 PM #2
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel Macro for transposing data based on a coloumn

    Take the semicolon off the prpID variable?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 06-20-2014, 02:32 PM #3
    Solus Rankin is offline
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Excel Macro for transposing data based on a coloumn

    Multiple potential problems. ASFAIK activecell.row is read only and activecell.count will always be 1.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."
    Reply With QuoteRegister To Reply
  4. 06-20-2014, 02:34 PM #4
    excelNoobN is offline
    Registered User
    Join Date
    06-20-2014
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    2

    Re: Excel Macro for transposing data based on a coloumn

    I removed the semicolon and edited the code a bit, still I am getting a run time error

    Sub TransData()
    Dim prpId As long
    prpId = 100011
    Dim prpIdRng As long
    Dim i As long
    i = 2
    While Cells(ActiveCell.Count, "a").Row <> Cells(Rows.Count, "a").End(xlUp).Row ' Till end of the sheet
    While (Cells(ActiveCell.Row, "a").Value = prpId and Cells(ActiveCell.Row, "a").Value = "") ' While the coloumn has a value and while it is the same as prpId
    If (Cells(ActiveCell.Row, "f").Value = "1") then
    Set Cells(i, "k").Value = Cells(ActiveCell.Row, "G").Value
    ElseIf (Cells(i, "f").Value = "2") then
    Set Cells(i, "l").Value = Cells(ActiveCell.Row, "G").Value
    ElseIf (Cells(ActiveCell.Row, "f").Value = "A") then
    Set Cells(i, "m").Value = Cells(ActiveCell.Row, "G").Value
    ElseIf (Cells(ActiveCell.Row, "f").Value = "B") then
    Set Cells(i, "n").Value = Cells(ActiveCell.Row, "G").Value
    ElseIf (Cells(ActiveCell.Row, "f").Value = "C") then
    Set Cells(i, "o").Value = Cells(ActiveCell.Row, "G").Value
    ElseIf (Cells(ActiveCell.Row, "f").Value = "D") then
    Set Cells(i, "p").Value = Cells(ActiveCell.Row, "G").Value
    ElseIf (Cells(ActiveCell.Row, "f").Value = "E") then
    Set Cells(i, "q").Value = Cells(ActiveCell.Row, "G").Value
    End If
    ActiveCell.Row = ActiveCell.Row+1
    Wend
    prpId = Cells(ActiveCell.Row, "a").Value
    i = i+1
    Wend

    Can you please help me in sorting this out.
    Reply With QuoteRegister To Reply
  5. 06-20-2014, 02:59 PM #5
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel Macro for transposing data based on a coloumn

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    I fixed your post #1 as a courtesy, now you need to make sure you're doing it from now on. Thanks.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VB Macro for Transposing Data in Excel 2003- small crisis
    By jkenf in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-21-2013, 12:24 AM
  2. Macro to create a button in z coloumn for each row that contains data and link a macro
    By yrk2k8 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-03-2013, 06:38 AM
  3. Transposing one column data to different columns based on their headings
    By bipindurgapal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2012, 06:35 AM
  4. Transposing sets of data based on formatting
    By scrchrds in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2011, 02:16 PM
  5. macro: changing color of coloumn header based on rows color
    By rajaid in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-22-2009, 06:43 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1