Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA Macro

Posted on 2008-06-25
8
Medium Priority
?
762 Views
Last Modified: 2011-10-19
Dear All,
Here is the issue:  I am attaching the spreadsheet with the macro.  But the macro only works for the first line (line 6).  I would like someone to help me with the macro that would not only for the first line 6 but work for as long as there is no empty line.  I would appreciate if someone would add also a button to start the macro.
Macro-Rafal.xls
0
Comment
Question by:rniedzia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 21867954
rniedzia,
There was no macro in the file when I opened it. Could you post it using the "Attach Code Snippet" checkbox?

Brad
0
 

Author Comment

by:rniedzia
ID: 21868040
Strange that it was not attached. here is the spreadsheet again with the code snipit.
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/25/2008 by niedziar
'
 
'
    Application.Run "RefireBLP"
    Selection.Copy
    Application.Run "RefireBLP"
    Range("D6:O6").Select
    Application.Run "RefireBLP"
    Application.Run "RefireBLP"
    Application.CutCopyMode = False
    Selection.FormulaArray = "=TRANSPOSE(FOAC_GetDataSources(RC[-2]))"
    Application.Run "RefireBLP"
End Sub

Open in new window

Macro-Rafal.xls
0
 
LVL 81

Expert Comment

by:byundt
ID: 21868292
Your workbook did not include the RefireBLP macro either. It seemed strange to run it so often, but I allowed those statements to remain in the revised Macro2 (see code snippet below).

I recommend using a Forms toolbar button to trigger your macro.

To install a Forms toolbar button (less flexible, but works on both Windows & Macintosh):
1) Open the View...Toolbars menu item and choose Forms toolbar
2) Click on the Command button icon (looks like a gray rectangle)
4) Now click and drag on the worksheet to draw your macro button
5) The Assign Macro dialog should now be open. Enter the name of the macro you want to run when the button is clicked. If you are doing this step later, then right-click the button and "Assign Macro" from the resulting popup.


To install an Active-X button (very flexible, but not Macintosh compatible):
1) Open the View...Toolbars menu item and choose Control Toolbox
2) Click on the upper left icon (triangle and ruler) to enter design mode
3) Click on the Command button icon (fourth down on left)
4) Now click and drag on the worksheet to draw your macro button
5) Double-click on the button, and its code pane will appear
6) Paste the suggested code on the resulting code pane
7) ALT + F11 to return to the worksheet
8) If you want to change the button label, right-click the button and choose "Properties". Enter the desired label in the "Caption" field, then click the X at upper right corner of the Properties dialog to close it
9) Click on the triangle + ruler icon once more to exit design mode


Brad
Sub Macro2()
Dim rg As Range
Set rg = [A6]
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
Application.Run "RefireBLP"
'Selection.Copy
Application.Run "RefireBLP"
'Range("D6:O6").Select
Application.Run "RefireBLP"
Application.Run "RefireBLP"
Application.CutCopyMode = False
rg.Offset(0, 2).Cells(1, 1).FormulaArray = "=TRANSPOSE('C:\Apps\AC\Clients\ACAddin.xla'!FOAC_GetDataSources(RC[-1]))"
rg.Offset(0, 3).Cells(1, 1).FormulaArray = "=TRANSPOSE('C:\Apps\AC\Clients\ACAddin.xla'!FOAC_GetDataSources(RC[-2]))"
'rg.Offset(0, 2).Cells(1, 1).FormulaArray = "=TRANSPOSE((RC[-1]))"    'How I tested the macro
'rg.Offset(0, 3).Cells(1, 1).FormulaArray = "=TRANSPOSE((RC[-2]))"
rg.Offset(0, 2).FillDown
rg.Offset(0, 3).FillDown
Application.Run "RefireBLP"
End Sub

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:rniedzia
ID: 21868588
Brad,

it is close to be working, but there is a little problem.  the macro does what it supposed to do, but only populates for Column C and D.  I want it to populate up to M.  Please let me know where is the control for it.
0
 

Author Comment

by:rniedzia
ID: 21868662
actually, it is not working.  in columns C & D i get the first field of an array.  I wanted to expend an array for each row.
0
 

Author Comment

by:rniedzia
ID: 21868967
it needs to go line by line first do the line 6 than move to 7.  preform the same procedure that i listed in my snippet.
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 21869191
Instead of this:
rg.Offset(0, 2).Cells(1, 1).FormulaArray = "=TRANSPOSE('C:\Apps\AC\Clients\ACAddin.xla'!FOAC_GetDataSources(RC[-1]))"
rg.Offset(0, 3).Cells(1, 1).FormulaArray = "=TRANSPOSE('C:\Apps\AC\Clients\ACAddin.xla'!FOAC_GetDataSources(RC[-2]))"
'rg.Offset(0, 2).Cells(1, 1).FormulaArray = "=TRANSPOSE((RC[-1]))"    'How I tested the macro
'rg.Offset(0, 3).Cells(1, 1).FormulaArray = "=TRANSPOSE((RC[-2]))"
rg.Offset(0, 2).FillDown
rg.Offset(0, 3).FillDown


Try it like this to include columns C:M. I prefer to use code that doesn't select anything. But since I don't know how RefireBLP is working, you may need to use the much slower code in the snippet
rg.Offset(0, 2).Resize(1,11).FormulaArray = "=TRANSPOSE('C:\Apps\AC\Clients\ACAddin.xla'!FOAC_GetDataSources(RC[-1]))"
rg.Offset(0, 2).Resize(1,11).FillDown


Sub Macro2()
Dim cel As Range, rg As Range, rw As Range
Set rg = [A6]
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
For Each cel In rg.Cells
    Set rw = cel.Offset(0, 2).Resize(1, 11)     'Columns C:M on that row
    Application.Run "RefireBLP"
    Selection.Copy
    Application.Run "RefireBLP"
    Application.Run "RefireBLP"
    cel.Offset(0, 3).Resize(1, 12).Select   'Columns D:O on that row
    Application.Run "RefireBLP"
    Application.CutCopyMode = False
    rw.FormulaArray = "=TRANSPOSE('C:\Apps\AC\Clients\ACAddin.xla'!FOAC_GetDataSources(RC[-1]))"
    Application.Run "RefireBLP"
Next
End Sub

Open in new window

0
 

Author Comment

by:rniedzia
ID: 21869305
wow - this works!!!  

now, stupid question would you be able to add this function into the macro:
=TRANSPOSE(FOAC_GetDataSources(B6))  - right now it is located in C column, so the spreadsheet would provide values in column AB, than we run macro gives us column C of =TRANSPOSE(FOAC_GetDataSources(B6)) and the rest of the macro as it stands right now?
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question