Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-25-2015, 02:30 AM
get4hari get4hari is offline VBA Macro code help Windows 7 64bit VBA Macro code help Office 2010 64bit
Novice
VBA Macro code help
 
Join Date: Sep 2015
Posts: 6
get4hari is on a distinguished road
Default VBA Macro code help


Dear All,

I am new to the Excel macros and VBA tools and however i have some design idea how My Tool needs to implemented, We need your support and assistance to get this created, please help here.

Need a help to create macro code by using VBA command Tool.

I have 3 sheets here.

A. Summary Tab (Here I would be uploading a input sheet )
B. Output Tab = required columns to be upload into a server
C. Input Tab = uploaded sheet info will be populated here

Problem Statement:

Input Tab:-> Data alignment command button ( after clicking, it should work as per below)

a) In column “D “ blanks Rows need to be removed
b) Towards to last, I mean in “ BN” column heading name to be added as “Total”
è Column header replacement
1. Business Agreement ID – replaced with Account Number
2. BP ID – replaced with Invoice Number


3. NMI/MIRN/DPI –replaced with NMI
4. Bill Start Date -Period From
5. Bill End Date -Period To
6. Peak Consumption– Peak
7. Shoulder Consumption –Shoulder
8. Off Peak Consumption-OffPeak
9. Capacity Value-Capacity
10. Service Charge-Service
11. Discount Amount($)-Discount
12. Usage and Service Charges-TotalIncGst
13. Total Amount Due -TotalDue
14. In Columns “O & P “ columns we have dates in the format of “YYYYMMDD ‘ and this columns should be replaced with DD/MM/YYYY format
15. Column Name heading –“ BO” name to be updated as “Temp ID” and it should do concatenate " D+A+O" columns

Output Tab: heading names i have already pasted here and now we have 2 command buttons.

-> Command button 1 - Data fetch & Refresh (After clicking, it should fetch the data from the Input data in the below format

a) Each row line item from input data sheet - > should create a 9 rows (times)
b) It should fetches the data based on
c) Based on the heading names in the Output sheet - >fetches the corresponding data from the sheet on below heading

Data should be populated based on heading Index and matching in all 9 columns except( I mean we are repeating row items)

Please help on this

Attached is the output file which has been updated manually , please please on this

Thanks & Regard,
Hari Prasasd B
Attached Files
File Type: xlsm Summary file for Bid creation-Output.xlsm (22.5 KB, 10 views)
Reply With Quote
  #2  
Old 09-25-2015, 02:31 AM
get4hari get4hari is offline VBA Macro code help Windows 7 64bit VBA Macro code help Office 2010 64bit
Novice
VBA Macro code help
 
Join Date: Sep 2015
Posts: 6
get4hari is on a distinguished road
Default

Can some one please help me on this request.
Reply With Quote
  #3  
Old 09-26-2015, 10:40 AM
charlesdh charlesdh is offline VBA Macro code help Windows 7 32bit VBA Macro code help Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

HI,

I was looking at your file.
If you need to change the headers why don't you place the header that you need instead of replacing them?
Also have you researched to see if you can come up with a "Macro?"
That too is a learning process.
Reply With Quote
  #4  
Old 09-26-2015, 01:54 PM
NoSparks NoSparks is offline VBA Macro code help Windows 7 64bit VBA Macro code help Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

What SamT is doing may be of interest. He's up to the "show us your code" point.
Reply With Quote
  #5  
Old 09-26-2015, 06:36 PM
get4hari get4hari is offline VBA Macro code help Windows 7 64bit VBA Macro code help Office 2010 64bit
Novice
VBA Macro code help
 
Join Date: Sep 2015
Posts: 6
get4hari is on a distinguished road
Exclamation

Inputs sheet - > Delete the Empty rows & replace heading names " but unfortunately code is not working .

Code:
    Sheets("Inputs").Select
       Dim i As Long, Lastrow As Long
       Lastrow = Range("D" & Rows.Count).End(xlUp).Row
       For i = Lastrow To 1 Step -1
        If Cells(i, "D").Value = "" Then
            Cells(i, "D").EntireRow.Delete
        End If
    Next
    
  
    

    Sheets("Inputs").Select
    Cells.Find(What:="Business Agreement ID", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Replace What:="Business Agreement ID", Replacement:="Invoice Number", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
HTML Code:
Kindly look into this on priority
Reply With Quote
  #6  
Old 09-26-2015, 09:10 PM
NoSparks NoSparks is offline VBA Macro code help Windows 7 64bit VBA Macro code help Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Have a look at your Input sheet and have a look at your code.

Is row 1 really the row you want as the top of your data?
Are the blank cells really in column D?
Reply With Quote
  #7  
Old 09-26-2015, 09:34 PM
get4hari get4hari is offline VBA Macro code help Windows 7 64bit VBA Macro code help Office 2010 64bit
Novice
VBA Macro code help
 
Join Date: Sep 2015
Posts: 6
get4hari is on a distinguished road
Default multiple header replacement code

Empty row delete = this is working fine and multiple header replacement code is not working , can you please get this fixed
Reply With Quote
  #8  
Old 09-26-2015, 09:41 PM
NoSparks NoSparks is offline VBA Macro code help Windows 7 64bit VBA Macro code help Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

No.

The code you show is only for replacement of the first header, and it works.
You'll have to do your own typing.
Reply With Quote
  #9  
Old 09-26-2015, 10:33 PM
get4hari get4hari is offline VBA Macro code help Windows 7 64bit VBA Macro code help Office 2010 64bit
Novice
VBA Macro code help
 
Join Date: Sep 2015
Posts: 6
get4hari is on a distinguished road
Default

No, Header replacement is not working and it highlights the error as "Sheets("Inputs").Select"

Please check
Reply With Quote
Reply

Tags
macro find and replace, vba macro

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Code to create a Macro Shazz Outlook 0 03-10-2015 09:52 AM
VBA Macro code help Looking Again for Another Code/Macro rsrasc Word VBA 5 11-16-2014 05:47 AM
Style in Use Macro-Need Help Optimizing Code freda0255 Word VBA 4 08-04-2014 07:35 PM
VBA Macro code help VBA Code for adding Macro to QAT OTPM Project 4 08-22-2013 01:33 PM
Need Macro code for Outlook gbaker Outlook 0 04-11-2013 10:29 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:37 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft