How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1377
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I have a table with for columns. In column A there is a list

Resolved Question:

I have a table with for columns. In column A there is a list of numbers from 01000 to 16000. I want to create a VBA to place a bottom border after every set of 1000. For example:
01000
01100
01300
02210
02220
03300
04200

A border will be placed between numbers 01300 and 02210. Another border will be placed between 02220 and 03300. Another will be placed between 03300 and 04200 and so on.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 1 year ago.

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

I am not exactly following what you want

The-PC-Guy :

can you provide a copy of your sheet to create some sort of visual reference

Customer:


























































































































































































































































































































PROJECT NAME
DIV.DESCRIPTON BASE BID
01000General Conditions $ 130,000
02150Abatement $ 122
02200Selective Demolition $ 400
02880Play Field Equipment and Struc $ 13,735
02900Landscaping $ 603
03100Concrete Forms and Accessories $ 691
03200Concrete Reinforcement $ 296
03330Architectural Concrete $ 1,013
03400Precast Concrete $ 1,000
03530Concrete Topping $ 36
03680Insulated Concrete Forms $ 180
04200Masonry Units $ 287
04700Simulated Masonry $ 225
04720Cast Stone $ 50
04730Simulated Stone $ 2,669
04860Brick $ 45
05120Structural Steel $ 368
05530Glass Railings $ 405
05550Stair Treads and Nosings $ 905
05720Ornamental Handrails and Raili $ 25,390
05800Expansion Control $ 240
06100Rough Carpentry $ 92
06110Wood Framing $ 16
06130Heavy Timber Construction $ 429
06160Sheathing $ 292
06170Prefabricated Structural Wood $ 28
07100Dampproofing and Waterproofing $ 18
07210Building Insulation $ 721
07310Shingles $ 14
07410Metal Roof and Wall Panels $ 48
08100Metal Doors and Frames $ 95,000
08210Wood Doors $ 1,600
08400Entrances and Storefronts $ 23,485
08600Skylights $ 6,322
09380Cut Natural Stone Tile $ 45
09510Acoustical Ceilings $ 7,226
09600Flooring $ 4,688
09720Wall Covering $ 144
10300Fireplaces And Stoves $ 76,338
10430Exterior Signage $ 1,000
10810Toilet Parititions $ 69,765
10820Shower Enclosure $ 26,610
10830Mirrors $ 48
11130Audio-Visual Equipment $ 20,000
11460Commercial Kitchens $ 250,000
12000Furnishings $ 882,500
14200Elevators $ 55,000
BID AMOUNT $ 1,700,089
OVERHEAD 2.00% $ 34,002
FEE 10.00% $ 173,409
GRAND TOTAL $ 1,907,500
The-PC-Guy :

not exactly what I had in mind, but it helps

The-PC-Guy :

if you see a paperclip icon at the top of your chat window

The-PC-Guy :

you can upload your excel file that way

The-PC-Guy :

says image

The-PC-Guy :

but works on excel files

The-PC-Guy :

that doesn't help either

The-PC-Guy :

I'm sorry you are experiencing problems with the attach tool

The-PC-Guy :

can you try this site

The-PC-Guy :

www.wikisend.com

The-PC-Guy :

you upload the actual xls file there

The-PC-Guy :

then they give you a file ID#

The-PC-Guy :

paste that ID# XXXXX

Customer:

see attached. The first 4 sheets is the full table. Then I have a proposal (5th sheet) which I have a macro which zeros out all of the rows with a zero value in column 4. Then I do the same for the final sheet. But I loose the bottom border which separates the 'division codes'. Then I have to manually input each border. I hope this explanation helps you better understand it.

The-PC-Guy :

inotherwords





7:12 PM



every time the 1000s place changes






7:12 PM



you want a blue bottom border across the row






7:12 PM



and you want that to be done with macro






7:12 PM



is this correct so far?



The-PC-Guy :

come back here when ready to continue

Expert:  The-PC-Guy replied 1 year ago.
Our chat has ended, but you can still continue to ask me questions here until you are satisfied with your answer. Come back to this page to view our conversation and any other new information.

What happens now?

If you haven’t already done so, please rate your answer above. Or, you can reply to me using the box below.
Expert:  The-PC-Guy replied 1 year ago.

inotherwords


every time the 1000s place changes


you want a blue bottom border across the row

and you want that to be done with macro



is this correct so far?

Customer: replied 1 year ago.

File ID: 929784

Expert:  The-PC-Guy replied 1 year ago.
here you go this macro will add the borders for you

http://ge.tt/5JbVeSv/v/0?c

let me know if any modifications are needed.

PLEASE REMEMBER TO RATE MY SERVICE

YOU DO SO WITH THE SMILEYS BENEATH THIS CHAT WINDOW
Customer: replied 1 year ago.

Looks good so far. Except it did not place a border between 12000 and 14200. I need to make sure that it does this through the last set of numbers between the 15000 and 16000. Also, I need the borders to stop at column E. Finally, I need to apply it to the Proposal sheet with the light line.


 


thank you.

Expert:  The-PC-Guy replied 1 year ago.
ok, when I run it, it puts one betwen 12000 and 13150, and another between 13900, and 14200. all the way to 16000 is this not correct?

I can make it only do columns A - E

did you want me to have it apply to all 3 sheets?
Customer: replied 1 year ago.


I do not need it done in the Summary worksheet. I need done on the Bid worksheet and the Proposal worksheet but with a light line (the top left line of the border format). Also, can you add this to be done after the delete 0 macros so after the macro removes all rows which have 0 values then the border macro runs.

Expert:  The-PC-Guy replied 1 year ago.
Ok, this should do it

http://ge.tt/7adVWTv/v/0?c

PLEASE REMEMBER TO RATE MY SERVICE

YOU DO SO WITH THE SMILEYS BENEATH THIS CHAT WINDOW
Expert:  The-PC-Guy replied 1 year ago.
are we all set?

http://ge.tt/7adVWTv/v/0?c

PLEASE REMEMBER TO RATE MY SERVICE

YOU DO SO WITH THE SMILEYS BENEATH THIS CHAT WINDOW
Customer: replied 1 year ago.

Almost there!


A few items to edit:


 


1. The border in the 'Bid' worksheet should be the thick one you previously had.


2. The border in the 'Proposal' worksheet is good with one exceptions; the border should go from column A to column D only. Currently it is going through column E.


3. Finally, I want to combine all three macros to one. This macro should perform the following task.



a. 'Proposal' worksheet. Delete all rows with a 0 value in the D column.


b. 'Bid' worksheet. Delete all rows with a 0 value in the D column. This has be done after 'part a' above. If not, since the 'Proposal' work sheet receives it's value from the 'Bid' worksheet, the Proposal will have a '#REF!' in the column D cell in some of the rows and the macro will not work. Unless you have a better way of doing it.


c. Add thick blue border to the 'Bid' worksheet.


d. Add the light border to the 'Proposal' worksheet.


 


thank you.

Expert:  The-PC-Guy replied 1 year ago.
alright, let me see what I can do.

So the deletions will happen first then the borders get added.

Correct?
Customer: replied 1 year ago.


Yes. Deletion first. If not you will delete the borders after you place them. This does not have to happen for the 'ICE Summary' sheet. That sheet is stagnant and comes in from another software.


 


Thank you.

Expert:  The-PC-Guy replied 1 year ago.
this should do it

http://ge.tt/4Q668Wv/v/0?c


PLEASE REMEMBER TO RATE MY SERVICE

YOU DO SO WITH THE SMILEYS BENEATH THIS CHAT WINDOW

Customer: replied 1 year ago.

One last thing. When the macro is complete with the Proposal worksheet. I would like to have the curser placed on cell B7. When the macro has completed with the B worksheet, have the cursor placed in cell D3. Finally, have the message box read 'BIDFORM IS READY!'


 


Thank you.

Expert:  The-PC-Guy replied 1 year ago.
um ok, no problem

but please remember to rate or I don't get paid for my time.

http://ge.tt/4eLBdWv/v/0?c

PLEASE REMEMBER TO RATE MY SERVICE

YOU DO SO WITH THE SMILEYS BENEATH THIS CHAT WINDOW
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1377
Experience: 20 years experience providing remote computer support
The-PC-Guy and 4 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.

Hello PC Guy,


I would like to enhance the macro we built last week. I am importing an excel worksheet into an existing workbook. The work sheet is called ICE to Excel. This export from the another software will open Excel and I would like the following to occur:


 


1. Enable Event Handler to run the following macro when the Excel workbook is opened.


2. Delete or ignore the last row 'Total Estimate and amount'.


3. Text to column first row to separate code values and description.


4. Run DelZeroRowsAddBorders Macro. (previously built)


5. The quantity of rows that is brought in will be unknown. Allow to add to Bid rows and Proposal Rows if more are required according to the ICE to Excel import.


 


I tried to upload the Excel sheet I'm working but I am not sure if you are getting it. If not send me the other link so I can send you the spreadsheet again.


 


Thank you,


 


Will


 

Expert:  The-PC-Guy replied 1 year ago.
I am not familar with ICE to Excel

but You can certainly have a macro run when a worksheet is opened in excel.

As far as doing all of these extra macros is a lot of work and goes far beyond the scope of what you originally asked for.

So we will need to open a new question on this site for each macro.

I can tell you how to do that when ready.

But I will also need more details about exactly what it is you are doing.

You will be importing the file to excel from an external program? The external program opens excel?

Or will you just open a particular workbook in excel, then have a macro fire off when the workbook is opened?

Customer: replied 1 year ago.

ICE is the name of the estimating program I use.


 


'ICE to Excel' is the name I gave to the worksheet I use to import the information from ICE to Excel.


 


The ICE program has an export to Excel feature in the reports menu. I named the report 'ICE to Excel'. When I export the report to Excel, it opens the workbook I browsed to and inputs the information into the ICE to Excel worksheet.


 


Did you receive the Excel spreadsheet I attached?


 


How do I go about opening a new question. I want to make sure I work with you. Send me the link to upload the Excel file to you.

Expert:  The-PC-Guy replied 1 year ago.
ok, I think I understand now.

So you will create the report in your external program, then browse to an exel workbook with the name 'ICE to Excel'

that has a sheet called

'ICE to Excel'

and these names will never change I would assume.

I will tell you how to upload the excel file.

First I need you to open a new question and I will post the upload instructions there to make life easier

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.
Customer: replied 1 year ago.


The Excel workbook is called Bidform. There is a worksheet in the Bidform workbook called ICE to Excel. There are two other worksheets that in the Bidform. None of the worksheet names ever change.

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions

Chat Now With A Microsoft Office Technician
The-PC-Guy
The-PC-Guy
Microsoft Office Technician
1377 Satisfied Customers
20 years experience providing remote computer support