Microsoft Excel

131K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi Everyone,
 
I am using one VBS which delete and Insert records from Excel. But need some adjustments here.
For example this script deleting 10 records and inserting 10 records into Excel, but replicating some records again at right side again and again after each run.
And my Excel has 3 multiple Sheet, but I need to insert into a particular sheet i.e. Sheet2
 
after execution I am seeing these defects
1. Legends moved to somewhere else,
2. row 1-6 repeated but right side.
 
Here is my CSV data.
But after second run data got inserts some other place.
 
Name,Location,Phone,Comment1,Comment2,comment3
"ABC","Pune",123,"Expert Value","! Easy","Popular"
"XYZ","Kol",567,"! Expert value",Easy,"!Credit"
"PQR","Mum",234,"NOT value","Value for money","Debit"
"RST","DEL",0,"Value","NO value","N/A"
"Ram","KOL",100,"NO Value","value","N/A"
"XYZ","Kol",567,"! Expert value","!Easy","!Credit"
"qwer","DEL",567,"Expert value","Easy","!Credit"
"cvbn","Pune",567,"! Expert value","!Easy","!Debit"
"rtyu","DEL",567,"! Expert value","Easy","!Credit"
"kllo","Pune",567,"Expert value","NOT Easy","!Bad"

Open in new window


Here is my existing VBS.
 
srccsvfile = Wscript.Arguments(0)
tgtxlsfile = Wscript.Arguments(1)
 
'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(, "Excel.Application")
'If not found, create a new instance.
If Err.Number = 429 Then  '> 0
  Set objExcel = 

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

How can I merge multiple rows into one cell delimited by a semi-colon  - Thank you
0
Hi Experts!

    I have some code that I'm using to create a checkoff list with.  The code was working great until I adjusted the control buttons at the top.  Basically all i did was space them out more evenly and made the timer box bigger.  In doing this I had to delete the first text box and it's checkbox at the top of the form to make room for the adjustment.   Once complete, I went to launch the macro, It's giving me a run-time error now.  Would one of you .vba guru's take a look at this to see where I'm going wrong at and what I need to do to correct it?  Attached is the run-time error itself along with the debug item that it's choking on within the form code.  Thanks a million for your help!

Run-time error
Debug Error within Form Code
0
Since moving to Office 365 I have had very sluggish performance in Excel.  I typicall work in workbooks stored in my one drive folder.
Often, when scrolling through cells with the arrow keys the entire workbook will freeze for several seconds.

I noticed in the File Screen, that I do not show a listing for our One Drive Business ....just a personal one which I do not use.  It also shows 'Loading services' that never changes?   See screenshot.

All shows normally in Word and Powerpoint, so this is just an Excel thing.  I have tried uninistalling and reinstalling Office 365 from the portal, but the problem remains.   I have also tried running both repairs from Control Panel - CHange..... still no different.

How can I correct?
stuck_excel.png
0
hi,

how to password protect excel/word/powerpoint file?

thanks.
0
I am attaching a file that has two Start entries (C4 and C31). This is just a sample set so this would be repeated for thousands of rows. I want to know how to do two things: how do I count the number of entries between each Start e.g. it should be 26 - but to do that for all the rows in the data set. Secondly how do I show the time elapsed (in hours) between each occurrence of start. (cell T7) - again for each occurrence  of this start...basically the idea is that a pump starts...does its thing. Stops and then starts again...
EE--calculating-rows-between-entrie.xlsx
0
Hi There,

I have an excel utility that creates a txt file containing metadata to be loaded into another system.  This has started to fail after the users upgrade to O365 / Excel 2016.

It creates the text file by creating an object referencing itself as a recordset:

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\Users\Jbloggs\Documents\user\ACTIVE\2017 07 - A&B\03 - Build\ExportFile.xlsm;
Extended Properties="Excel 12.0 Xml;
HDR=No;
IMEX=1";

However when this code is opened I receive the following error:

Code Running
Error...

Error Message

I have tried this on another machine with Excel 2016 and this code works without issue.

Any guidance would be greatly appreciated.

Many Thanks,
Mark
0
The attached example file contains three identifier columns with the main identifier being a code in column C.
There are duplicate codes at random down column C - Rows 3 & 4,  114 & 115, 185 & 186, and 196 & 197, are examples shown in red.

The objective is to have the contents of the lower duplicate row added to the contents of the cell above and then the lower row to be deleted.
The columns affected are from D to KQ.

The actual files have about 60,000 rows.
Example-file-with-duplicate-ID-code.xlsx
0
if someone has CTC 4.7 L per annum(Delhi, India),
what should be salary breakup per month?
0
Hello Experts,

Attached are two files, the MS Access db where I am trying to create multiple records based on the comments fields in the one record. The MS Excel spreadsheet contains a sample of what I am trying to accomplish.

I am able to move the records to a new table, but I am lost on the step where I want to duplicate the record based on how many comments are in the note field.

The attached Excel spreadsheet is a sample of what I am trying to accomplish based on MS Access ID=621.

Can you please help me out?

Thank you,

Steph_M
Sample.accdb
SampleNewRecord.xlsx
0
[Webinar] How Hackers Steal Your Credentials
LVL 9
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Someone has created this Macro for me to import data from a number of workbooks into the one in which the macro is running.  All workbooks are in the same folder.  Trouble is, although this runs fine on a Windows PC (Excel 2016), it doesn't run on a Mac (also 2016).  It comes up with an "Run-time error '53' File not found".

Incidentally, the last line calls another Macro I recorded that just Word Wraps a column in the workbook.  The last line of that calls another macro that adds in some text to say that the macro has run successfully.  I don't think the error is about the calls but if you can suggest how to add the lines into the first Macro, below, that would be great.  

Any ideas what is preventing this run on a Mac?
Thanks

Sub ConsolidateResults()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim rngDst As Range
Dim rngCopy As Range
Dim strFileName As String
Dim strPath As String

    Application.ScreenUpdating = False

    strPath = ThisWorkbook.Path & Application.PathSeparator
    Set wbDst = ThisWorkbook
    Set rngDst = wbDst.Sheets("Input Data").Range("F5")

    strFileName = Dir(strPath & "*.xlsx")

    Do

        Set wbSrc = Workbooks.Open(strPath & Application.PathSeparator & strFileName)
       

        Set rngCopy = wbSrc.Worksheets("Copy").Range("F1:F40")
 
   
        rngCopy.Copy
        rngDst.PasteSpecial xlPasteValues

        wbSrc.Close SaveChanges:=False

        Set wbSrc = Nothing

        Set rngDst = …
0
How do i write a nested if statement for the following?

If greater than  14 but less than 16 return £10000
BUT If greater than  16 but less than 18 return £20000
AND if greater than 18 return £30000

I tried =IF(AND(C9>=14,C9<16),10000,0)
Which returns £10000 however when i try nest it, everything just returns #VALUE?


Thank you
0
Hello Experts,

I am trying to write  vba code to auto indent cells based on cell content.
Capture.PNGAsm Part: indent by 2
Operation: Indent by 3
Something like that
0
Hi Guys,

I need your urgent help .

There is a workbook 1 with Sheet1 and Sheet2
I have added some value in sheet1
then In sheet2 I have added a formula like "=B3+Sheet1!B4" and "=Sheet1!D4+Sheet2!E3"

and saved the workbook as Test1.xlsx

now from "Test1.xlsx" , I have copied the sheet1 data and pasted it in New Workbook "Sheet1" sheet. And then copy the Sheet2 data from Test1.xlsx and pasted it in "Sheet2" sheet in new workbook.

When I have pasted the sheet2 data it shows me the formula like "=B3+[Test1.xlsx]Sheet1!B4" and "=Sheet1!D4+Sheet2!E3"

Note :- In both the workbook the sheets name are same.

My Question is I want to update the formula in new workbook without linking with "Test1.xlsx" workbook.

i.e my formula in new workbook in "Sheet2" would be  "=B3+Sheet1!B4" and "=D4+Sheet2!E3".

I want to do this task without "Edit" link update option or without replace logic. or Without any third party tool.

Is there any other option or way to remove the link and keep the formula only?

Appreciate your help :)

Thanks,
0
When I replaced the JARs of POI-3.9 with POI-3.16, I am getting a popup "We found a problem with some content in file.xlsx. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." while opening the downloaded excel. After clicking Yes, I am getting another popup "Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded."

I have used below JARs.
1. poi-3.16.jar
2. poi-ooxml-3.16.jar
3. poi-ooxml-schemas-3.16.jar
4. xmlbeans-2.6.0.jar
5. commons-codec-1.10.jar
6. commons-collections4-4.1.jar
7. commons-logging-1.2.jar
8. junit-4.12.jar
9. log4j-1.2.17.jar

Please suggest a proper solution.
0
People,

Can anyone here please assist me in how to show which data is not available on the two worksheets to be compared?

Source: In Policy worksheet
Comparison: Dangerous Extension worksheet

The result can be in the form of the third Worksheet.

Thanks
FileExtensions.xlsx
0
Good day Guys.For some of you who knows how to lock the rows of the cell,I'll be needing your help!!!
I want to lock only the formatted cells in a row (not the entire row).There's a "Pending"  and "Closed" dropdown list.I want to lock the specific data in a row after I click the "Closed"

Please see the attached file below.
I'll be happy and really appreciate for your suggestions/help.
0
Hi Guys, I have a Macro called PL Star Summary on the "Control" tab which uses a Worksheet Function which adds up data in a Tab of the attachment called "PL Star IMM_FX_Trading" by Portfolio name and populates a summary of the data. 3 Portfolios called FX_FLOWS_PR, PB_FX_PR & RETAIL_FX_PR should be WORKSHEETFUNCTION(SUM * -1) so an IF statement needs  to be inserted into the code whereas all the other Portfolios will remain SUM* 1. The subprocedure is called "CalculateNumbers" and looks like this:

Sub CalculateNumbers(ByVal strSheetName As String)
    Dim intTyplogolyCol As Integer
    Dim intPFCol As Integer
    Dim intPLCol As Integer
    Dim strPLColName As String
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim intHeaderPFCol As Integer
    Dim intHeaderPLCol As Integer
    Dim strHeaderPFName As String
    Dim strHeaderTypologyName As String
    Dim rRange As Range
    Dim dblPL As Double
    Dim strRange As String
    
    Sheets(strSheetName).Select
      ActiveSheet.AutoFilterMode = False
    If strSheetName = "PL Star MTM" Then
        intTyplogolyCol = 7
        intPFCol = 4
    End If
    If strSheetName = "PL Star IMM_FX_Trading" Then
        intTyplogolyCol = 7
        intPFCol = 4
    End If
    
    If strSheetName = "PL Star Accrual" Then
        intTyplogolyCol = 7
        intPFCol = 4
    End If
    
     If strSheetName = "PL Star MTM Accrual" Then
        intTyplogolyCol = 7
        intPFCol = 4
    End If
    
    'Get PF & 

Open in new window

0
Good day

I am trying to colour blocks in PowerPoint based on the colour of cells in Excel. Essentially what I am doing is using a table in Excel and updating these values (not too regularly). Based on the cell value in Excel, I would like the block in PowerPoint to be a certain colour.

My initial thought was to use a conditional formatting method to change the cell colour in Excel, and then copy that same colour (using RGB numbers) to PowerPoint using a macro.

Would this be possible? I have not yet got around to trying to write the macro as I thought I may find a skeleton upon which to develop my solution.

Thanks in advance!

Laevashan
0
Salesforce Made Easy to Use
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Hi
   How to create a macro for exporting the excel data into access database. Can anyone please help me.
0
I have two questions regarding hyperlink vba coding.

Question 1:

I would like to display the text "Click here" instead of the actual URL in each row. What do i need to do to achieve this?

Currently i am using the following code to generate the hyperlink

Dim i3 As Integer
Dim s3 As String

For i3 = 4 To Range("H:H").Cells.SpecialCells(xlCellTypeLastCell).Row
    s3 = "https://maps.google.com/maps?q=" & Trim(Range("H" & CStr(i3)).Text)
    ActiveCell.Hyperlinks.Add Range("Q" & CStr(i3)), s3
    Range("Q4").Font.Color = RGB(248, 248, 248)
    Range("Q4").Font.Underline = False
    Range("Q4").Value = "GoogleMap"
Next i3

Open in new window


Question 2:

What do i need to add in the code to display the text "BLANK" if the hyperlink only contains this

"https://maps.google.com/maps?q="
0
If I have a spreadsheet of data whereby column A gives me the name of someone, and column E gives me the answer 'yes' to liking ice cream; can I populate a new worksheet with the name provided in column A, provided the condition is met that Column E is 'yes'?
0
i want to export a report in excel format without open a crystal report could you suggest me that how can be implement this?
0
I worked on a large translation project in 2013, and the translation agency I worked for had an excellent solution for handling the terminology during this large translation project that involved several different translators co-operating in real-time using Google Docs (now Google Drive):

It was an Excel workbook uploaded online, with the column headers that are in the PDF I have uploaded here. You can see in this PDF that each translator could enter the Source term, Target term, Author first name, Definition, Source, Comments/questions, all in real-time (so that I could see the moment someone added a new term, or modified an existing term, or added a question or comment for a term, and immediately respond if needed.

I just wonder how I can create this myself (in this case, it was the translation agency that had set everything up)?

I have a Google Drive account already.
ITC-Termbase-project-Atlas-Copco-201.pdf
0
Hi there,

I am interested in learning how to use Power Pivot/View and would like to know of any suggestions for the best free, online information/tutorial? I have done the usual Google search but it comes up with a million different sites so would like to know from an expert's point of view, where is the best place to start?
0

Microsoft Excel

131K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.