Microsoft Excel

133K

Solutions

38K

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

I am trying to create a combination of columns data.

in book 1, i have 2000 company names. in book 2, i have 150 titles.

i want to combine these two books so that for each company name in col A, there are 150 rows with each title in one of them.
end reslt will be the permutations of 2000*150

example

book 1
AgentsX
Ain't Life Grand Investments
Aire Serv Heating and Air Conditioning®
Aire Serv UK

book2
CEO
founder
Director


book3
cola              colb
AgentsX       CEO
AgentsX       Founder
AgentsX       Director
0
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

column highlighted in red colour nothing to do with that and we don't have to consider the red highlighted colour column, No role of that data
the coloumn highlighted in yellow colour we have to work on that
copy that data and paste that data to sheet2 each first empty row of each  row
in sheet2 also we don't have to consider the red highlighted coloumn no role of that coloumn
I have posted the question  before also  the code was proper but my data  is alpha numeric in red highlighted coloumn so that code does't work so that's y don’t consider red highlighted coloumn
see the sample file
Book1.xlsm
0
Hello Sirs,

Below Code was working perfect but now having problem while saving file as PDF, it still save the file but without any extension e.g. .pfd or .xlsb, to open the file i have choose the file type then only I can open it.


 
Dim FileExtStr As String, pdfName As String, filesavename As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim FilePath As String
    Dim FileName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False

        Set Sourcewb = ActiveWorkbook
        Select Case MsgBox("Do you want to save as XLS/PDF, Yes for Xls, No for PDF", vbYesNo Or vbExclamation Or vbDefaultButton1, "File format")
        
        Case vbYes
            'Copy the sheet to a new workbook
            ActiveSheet.Copy
            Set Destwb = ActiveWorkbook

            'Determine the Excel version and file extension/format
            With Destwb
                If Val(Application.Version) < 12 Then
                    'You use Excel 97-2003
                    FileExtStr = ".xls": FileFormatNum = -4143
                Else
                    'You use Excel 2007-2016
                    Select Case Sourcewb.FileFormat
                    Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                    Case 52:
                        If .HasVBProject Then
                            FileExtStr = ".xlsm": FileFormatNum = 52
                        End If
                

Open in new window

0
Hi! I need help to convert a "For Next" loop to a "Do While" Loop.

As of Now, the "For Next" Loop works perfectly but when I converted it to a "Do While" Loop, it does not work.

Please help to edit the "Do While" Loop so it works.

I have attached an example workbook where the codes should be used :)
ForNext Version:
Sub ForNext()

Dim row1, row2, counter As Integer

For row1 = 3 To 10

For row2 = 2 To 553

    If Cells(row2, 3).Value = " Su Mura" And _
    Cells(row2, 2).Value = Cells(1, 7).Value And _
    Cells(row2, 5).Value = Cells(row1, 7).Value Then
    counter = Cells(row2, 4).Value + counter
    
Cells(row1, 8).Value = counter
counter = 0
    
    End If
    Next
    Next
    


End Sub

Open in new window


Do While Version
Sub DoWhileVersion()

Dim row1, row2, counter As Integer
row1 = 3
row2 = 2

Do While row1 < 11


Do While row2 < 554

    If Cells(row2, 3).Value = " Su Mura" And _
    Cells(row2, 2).Value = Cells(1, 7).Value And _
    Cells(row2, 5).Value = Cells(row1, 7).Value Then
    counter = Cells(row2, 4).Value + counter
    
Cells(row1, 8).Value = counter

counter = 0
End If
    
row1 = row1 + 1
Loop

row2 = row2 + 1
Loop



End Sub

Open in new window

NewProjectBook.xlsm
0
Modify the Vba code
Vba Code with data attachment-the vba code works fine in this file but my data is modified so look into the Now my file is modified attachment
Now my file is modified attachment- this is my modified data i need the vba code for this file to do the same thing which vba code with data attachment has done copy and paste the data of sheet1 and paste to  first empty cell of each row
Vba-Code-with-data.xlsm
Now-my-file-is-modified-and-it-look.xlsm
0
this highlighted colour is only for understanding purpose
Conditionaly copy the data from sheet1 and paste to sheet2
Kindly see the sample file
i have to do this only by vba
Sample128.xlsm
0
EE Pros,

Can someone help me with solving the reverse equation for the formula in Cell F8?

Thank you in advance.

B.
Solve-Reverse-Eq.xlsx
0
I am creating a code that would make all odd rows yellow and all even rows white

It's not working properly right now so I would appreciate any help editing it! The error is that the subscript is out of range

Sub loops_exercise()

Dim R As Integer
    R = 1
    Do While R < 20
   
Dim C As Integer
For C = 1 To 20

       If R Mod 2 = 0 Then
           Cells(R, C).Interior.ColorIndex = RGB(255, 0, 0)
       Else
       
           Cells(R, C).Interior.ColorIndex = RGB(2, 0, 0)
       End If
       
Next

R = R + 1
Loop
    
End Sub

Open in new window

0
Hi Experts

I have the following if statement which is not working...cannot see error..

Booking to Appt = IF(SLA[Work Type]="CLCM Call Booking", IF(SLA[Appt Made - Appt]<=SLA[Customer CLCM Call Booking SLA],"In Target",

IF(SLA[Work Type]="IMA HLM OHA Onsite", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHA Onsite SLA],"In Target",

IF(SLA[Work Type]="IMA HML OHP Onsite", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Onsite SLA],"In Target",

IF(SLA[Work Type]="IMA HML OHP Offsite", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML OHP Offsite SLA],"In Target",

IF(SLA[Work Type]="IMA HML TIMA/VIMA", IF(SLA[Appt Made - Appt]<=SLA[Customer IMA HML TIMA/VIMA],"In Target",

IF(SLA[Work Type]="IMA Network OHP", IF(SLA[Appt Made - Appt]<=SLA[Customer Network OHP SLA],"In Target",

IF(SLA[Work Type]="IMA Network OHA", IF(SLA[Appt Made - Appt]<=SLA[Customer Network OHA SLA],"In Target",

IF(SLA[Work Type]="IMS", IF(SLA[Appt Made - Appt]<=SLA[Customer IMS SLA],"In Target","Out Of Target"))))))))))))))))
0
Help me edit this code to achieve this end result. As much as possible, please keep the format the same for "select case" and "Loop"
Sub QuestionFive()

    Dim r, c As Integer
Do While r < 20
    r = 1
Do While c < 20
    c = 1


          
Select Case Cells(20, 20)
Case (r + c) Mod 2
    Cells(r, c).Interior.Color = RGB(255, 255, 0)
     Cells(r, c).Interior.Color = RGB(255, 255, 255)

End Select

r = r + 1
Loop

c = c + 1
Loop
End Sub

Open in new window


de.JPG
0
Cloud Class® Course: MCSA MCSE Windows Server 2012
LVL 12
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

I am looking to build a VBA macro that will take columns A and B in the attached sample and copy and paste each name/ username into the blank areas.
sample.xlsx
0
Hallo,
I am looking for a solution how to delete my files in a depository at once. I made a list in excel (column A) with files with their full names, which I want to delete from this depository:
H:\Dokument\ToClean\12354-aabb.pdf
H:\Dokument\ToClean\12222-aaggh.zip
My files for deleting are in various forms (pdf, word, zip, doc) and have various names.

Until now I could find makro, vba in Excel only for deleting 1. all files from the depository, 2.files with a specific format 3. a specific file. Non of these solutions are appropriate for me.
One solustion was supposed  to be suitable, but it does not work:

Sub DeleteButton()
Dim i As Long
 
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Kill Cells(i, 1).Value
    Next i

End Sub

This makro has a problem with the "Kill Cells(i, 1).Value". I would be thankfull if you could help.
Thank you.
0
Hello,

I need to create a record from multiple lines of text in csv file. I'm parsing log information. Basically, the record starts at 2018-05-2018 and ends at the next occurance of the  2018-05-2018.   For example:

2018-05-17T11:kfjsadlkfadklgjkaddjfj   dfalksdfsrhtioqeuih     du839457heir5834   odioiqweethen554viir dofjaweweoprweoitrhoiwer
dajrfawdkhboiyib   kdfjag ojern u
2018-05-17T11:32:03.fkdslfjds  dkfj;d ej96nt []

2 records would be created out of the example above:

Record 1 = 2018-05-17T11:kfjsadlkfadklgjkaddjfj   dfalksdfsrhtioqeuih     du839457heir5834   odioiqweethen554viir dofjaweweoprweoitrhoiwer dajrfawdkhboiyib   kdfjag ojern u
Record 2 = 2018-05-17T11:32:03.fkdslfjds  dkfj;d ej96nt []

Currently the file is in notepad.

I have Excel, Access or notepad++

Once I have the multiple lines into 1 record, I can parse out the information I need, but I'm not sure how to combine the lines to make one record.

Any suggestions?

Thank you?
0
copy the data from sheet1 and paste the data to sheet2 conditional
plz see the sample file
i have to do all this by vba only
Book1.xlsm
0
Hi,
I would like a formula modified to replace a manually input matching cell
Please see attached sheet with explanation.
Many Thanks
Ian
Matching-Names.xlsx
0
if a sheet has duplicates rows then put the summary in sheet2
I need this type of result
all this I have to do by vba only
this highlighted colour is only for understanding purpose
see the sample file
Book1.xlsm
0
what I want is to combine sheet1,sheet2,sheet3,sheet4,sheet5 into 1sheet
so for the same
I have putted sheet1 ,sheet2,sheet3,sheet4,sheet5  all cells data in the same place after putting (,)
so all this I have to do by vba
Sample-file.xlsm
0
Hi Experts

Hope you can help

I am putting together a report for my month end meeting and it covers three sets of costs I have negotiated with three potential candidates to handle our freight

The data is OK but on the third sheet Id like to put together a simple SWOT analysis sheet based on the three candidates

What Id like to do is to have a drop down list (in D2) to select the desired Candidate and ‘pull’ the relevant data into each corresponding square. For example, in column V, I have Strengths against each candidate. When I select candidate 1, for example, Id like to extract the information from cells V4 to V10, into the Strengths section of the SWOT Model and display this in D5 to D16?

Ive tried methods of indexing and merging to try and get the block of data for each section to appear when the candidate is selected but to no avail.

I have attached the sheet (the sheet in question is titled SWOT) and I have placed the text in RED what Id like to see in the sections when the corresponding candidate is selected (ive used Candidate 1 for this purpose)

Any suggestions would be very welcome

J
Forwarder-Benchmark.xlsx
0
I am having to use Power Query in Excel 2013.  I want to create a query to connect to the Domain Controller to get a list of groups.  Unfortunately, I am more familiar with the later versions of Power Query and I am having difficulty in creating the connection when I don't have the New Query\From Other Sources\From Active Directory.

Could any one help?

I am not able to upgrade Excel 2013 and Power Query at this time.
0
Cloud Class® Course: Microsoft Office 2010
LVL 12
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Hi All,

There is an urgent requirement for excel macro which will extract the required data from the multiple analysis report(more than 500 excel files) and generate one invoice excel file (Filename: Invoice).

input data to extract
**Fix rows and columns in each analysis file.**
Rows 23 to 34

and required columns are
Column B and C

Sample input data in input excel files

and required output
Column B data | Column C data | Discounted Rates (Need to add by code)| Amount in USD (Need to add by code)
Please find attached sample output file

Thanks,
Shailesh
Analysis_1.xlsx
Analysis_2.xlsx
Invoice.xlsx
0
I have a spreadsheet with a list of part numbers and a list of plant codes that I need to generate a new sheet where I have the list of part numbers and the same plant code for all of the part numbers, then I need to append the same list of part numbers with the next plant code.  Can someone help me with a macro or some way to accomplish this as I have over 70 plant codes and 12k part numbers.  Example below.  I have two separate sheets, one that has all of the part numbers and one that has the list of plant codes

PartNumber     Plant
12345                WW01
22322                WW01
22333                WW01
33444                WW01
44333                WW01
12345                WW99
22322                WW99
22333                WW99
33444                WW99
44333                WW99
12345                US01
22322                US01
22333                US01
33444                US01
44333                US01
12345                US02
22322                US02
22333                US02
33444                US02
44333                US02
0
Hi All,

I'm not sure how to do data formatting using a SQL Statement in Oracle database.

Is it possible to have output from a SQL statement appear like (see attached) or in an excel format using a pivot table?
EE-Question.xlsx
0
Originally posted here:

https://stackoverflow.com/questions/50329940/vbs-convert-tab-delimited-csv-file-to-xlsx

I have already posted a similar question before which converts CSV files to xlsx.

This question can be found here:

https://stackoverflow.com/questions/49294151/vbs-loop-through-multiple-csv-files-in-a-folder-and-convert-the-files-to-xls

Code: VBS

'Constants 
Const xlOpenXMLWorkbook = 51             '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50                     '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56                       '(97-2003 format in Excel 2007-2016, xls)

' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"
strXLS = "xls"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

strFolder = "B:\EE\EE29088597\Files"

' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)

' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False

' Process all files
For Each objFile In objFolder.Files
    ' Get full path to file
    strPath = objFile.Path
    ' Only convert CSV files
    If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
        ' Display to console each 

Open in new window

0
Hi,
I would like the attached formula to be corrected to achieve the correct answers as shown in A12:A28
Many Thanks
Ian
COUNT-SERIES.xlsx
0
Hi ,


I have an annoying error showing up in PowerPivot which is not letting me to build any DAX functions in PowerPivot, please help me.

Any time I go to create a formula I get an error " Unhandled exception has occurred in a component in your application.  If you click Continue, the application will ignore this error and attempt to continue. Details below.



----------------------------------------------------------------------------------------------------------
See the end of this message for details on invoking
 just-in-time (JIT) debugging instead of this dialog box.

 ************** Exception Text **************
 System.InvalidCastException: Return argument has an invalid type.
    at System.Runtime.Remoting.Proxies.RealProxy.ValidateReturnArg(Object arg, Type paramType)
    at System.Runtime.Remoting.Proxies.RealProxy.PropagateOutParameters(IMessage msg, Object[] outArgs, Object returnValue)
    at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
    at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
    at Microsoft.Office.Interop.Excel._Application.get_ActiveWorkbook()
    at Microsoft.AnalysisServices.XLHost.Addin.GeminiRibbon.currentClientWindow_InFormulaBarChanged(Object sender, EventArgs e)
    at Microsoft.AnalysisServices.XLHost.Modeler.ClientWindow.OnInFormulaBarChanged()
    at …
0

Microsoft Excel

133K

Solutions

38K

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.