Microsoft Excel

134K

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

tally_duration.xlsxtally_duration.xlsxI have a raw data set that includes hourly pH values at multiple sites ("Sonde_Names") for two three-month periods: one in year 2013 and another in year 2014.

For each of the "Sonde_Names", I need to generate a list showing the number and duration of events where the pH is below a value of 5.5 (the field "BlwThresh" indicates which records are below 5.5). An 'event' is defined as one or more contiguous values of 1 in the "BlwThresh" field. This list also needs to include the associated values for "Sonde_Names", "Year", "Month", "Day", and "Hour", in which the event began.

(If possible, the "Event_num" field should reset to a value of 1 when starting to process each unique set of "Sonde_Names" and "Year" values.)

The worksheet named "tally_duration" shows a few manual examples of the objective.

Many thanks in advance for a (hopefully portable) automated solution.
0
Angular Fundamentals
LVL 12
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Some time back we placed a question on how to read data from an internet page and save the specific content to an excel workbook (maybe it was a VBA).  We tried to search it in EE but can't find it.  So hope we can get the answer again.  We have certain web pages that we need get a specific data and save it's contents to an Excel.  For example, a user logs into a specific web page and register his work on it.  If a non-user views it, it always present the last user who saved his entry.  We want to read that user name and save it in the excel workbook.  The user name will always be in the specific location.  What is the best and automated manner in pulling data from a website into Excel?
0
Using Excel, seems like there is a way to BOTH freeze the top row AND a column to the left using some combination of:

Split

&

Freeze

Not VBA  code, but using the main features of the program itself.  However, can't figure it out.  What am I missing?

Thanks!

OT
0
We have an Excel that has a series of columns as filters and want to know if a "filter" has been set without going column by column to identify it.  Usually, when there are maybe 5 or 10 columns, we just look for the upside-down triangle which indicate a filter has been set in that column, but this excel has a lot of columns.  Or, we just disable the filters and then select columns >> enable filters again.

Is there a way that we can know that a filter has been set? (we don't need to know the actual column only if there has been any set).
0
Hi,
I would like a formula to establish identical values within an index range.
Please see attached
Many thanks
Ian
identical-values-within-range.xlsx
0
Hello experts,

I would like to create a procedure to create multiple folders based on path reported in column A.
If folder cannot be created skip cell and continue to next one.

I have initiate the first part.
Could someone help me to finish the rest of the procedure and add the error conditions?
I attached example file.

Thank you very much for your help.
Create-folders-based-on-column.xlsm
0
Hello experts,

In order to properly manage some of the procedures reported in my personal.xlsb file I am looking for a procedure part related to msgbox vbyes, vbno.
The idea is to prior to run the procedure I would like to display a msgbox which says: "Are you sure that all the requirements related to this procedure have been properly set up such as: column format, cells values etc..? If so click on Yes to continue else click on No.

Something like this:

Sub MessageBoxExample()
    Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
 
    ' Promt
    strPrompt = "Ask Your Question Here, OK?"
 
    ' Dialog's Title
    strTitle = "My Tite"
 
    'Display MessageBox
    iRet = MsgBox(strPrompt, vbYesNo, strTitle)
 
    ' Check pressed button
    If iRet = vbNo Then
        MsgBox "NO!"
    Else
        MsgBox "Yes!"
    End If
End Sub

Open in new window


What is the best practice to set up msgbox related to vbyes and vbno questions-answers?

Thank you very much for your help.
0
Hello Experts,

I have the following procedure which allows me to rename folders:
Sub Rename_Folders()
Const FILEPATH As String = "C:\"
Dim strfile As String
Dim filenum As String
strfile = Dir(FILEPATH)
Dim lr As Long
Dim ws As Worksheet
Dim old_name, new_name As String
Set ws = ActiveSheet
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = 2 To lr
''strOldDirName = FILEPATH & Sheets(1).Cells(i, 1).Value
''strNewDirName = FILEPATH & Sheets(1).Cells(i, 3).Value
strOldDirName = ws.Cells(i, "A").Value
strNewDirName = ws.Cells(i, "B").Value
Name strOldDirName As strNewDirName
Next i
End Sub

Open in new window


I would like to properly manage errors such as:
1-If folder reported in column A doesn’t exist skip the cell, display msbgox “Folder cannot renamed as it doesn’t exist” and continue with the next cell
2-If target folder reported in column B cannot be renamed, display msgbox “Folder cannot be renamed”

Please find attached example file.

If you have questions, please contact me.
Rename-folders-based-on-columns.xlsm
0
when i hit 00 to get the results in sheet 2

is not working properly and need eliminated the arrows too

x33333.PNG2n_fix_00_.xlsm
0
Hi,

I want to apply password to PDF file while saving PDF file using VBA code. What will be the code for this?

Thanks
0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Hi,
I would like a formula to round an if statement
Please refer to attached
Many thanks
Ian
round-if-statement.xlsx
0
Vba code to send sms from excel  by api
I will send msg to only 1 number that is my number and my number will be fixed it will not change
let my number be  1234567891
and api is xyz
And message that i have to send to my mobile is ALERT

I have to do this by vba only
In my case my mobile number is fixed   so i don't  need to enter my number manually
the code will run and sms is sent
0
I need to compare 5 columns in a pivot table to determine duplicates.
 I need to compare columns B  D  E  F   and  H -  I did not know how to set up the pivot table so it is incomplete.
 Then I need a count of duplicate values when the 5 columns match.


*I deleted original question due to lack of information and have submitted this as a new question - sorry for any misunderstanding.
0
Hi

I am facing a problem, countifs function on excel is not working for columns with different datatype.

Please have a look

Cheers
K
kk.xlsx
0
Hi,

I'm trying to loop through the sheets of the workbook. So the logic is if the sheets name matches Dino List's range("E" & i). value then it copies whole range and pastes it onto the matching named sheet. But I'm stuck with the for loop on line 'Next i'. It says 'next without for'. Don't know where I'm wrong?

Sub dinosaur()

Dim i, j As Integer
Dim diet As String
Dim ws As Worksheet
Dim lastrow As Long

 lastrow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
 

Sheets("Dino List").Activate

For Each ws In ActiveWorkbook.Worksheets
   For i = 2 To lastrow
        diet = Sheets(1).Range("E" & i).Value
        If ws.Name = diet Then
        Sheets(1).Range("A:K").Copy
        Worksheets(diet).Activate
        ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
        ActiveSheet.Paste
   Next i
Else: Next ws
End If
End Sub

Open in new window

0
To remove duplicate line if this line is based on a repeated route and also add 'Y' in column F to indicate that the next line was removed.

One can filter column b, c and d to be all equal to 0 and will see all the same route appear in column A.

See attached for detailed examples and comments.

And of course if there are more than 1 duplicates meaning like this:

108.004.600 - 108.004.600      0      0      0      84.75291788  
108.004.600 - 108.004.600      0      0      0      1.968908303  
108.004.600 - 108.004.600      0      0      0      2.417233924  
108.004.600 - 108.004.600      0      0      0      1.713124927  

then the result should be like this (3 lines below are removed) :
108.004.600 - 108.004.600      0      0      0      84.75291788   Y

and column G states how many lines were removed.

if there is no duplicate, for example:
405.091.200 - 405.091.200      0      0      0      6.337535366

then do nothing for this line.

Same concept applies to all.




Thanks !
Row-Indicator-Remove-Duplicate-EE.xlsx
0
I have some specific questions relating to Excel vba, Word vba and MailMerge.

My questions are:
1. I can get this instruction to work from Word vba:
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="FirstName"
I can access a Word document from Excel vba, however this instruction fails. So my question is can this instruction
be issued from an Excel macro?

2. From Word it is possible to format the output of a MailMerge field using Toggle Field Codes. How can this be done from a vba macro? I have managed to do it using find and replace but surely there is a better way?

3. From an Excel macro I can invoke a simple Word macro, with code like:
Set WD = CreateObject("Word.Application")
WD.Run "runTxtConversion", txtFile
For me this works fine so long as there is only a single Word document open.
How can I make it work if there are multiple Word document?

I would be grateful for either direct answers or references to books or on-line documents that can answer my questions.

Many thanks,

Peter
0
Hello
I need your help please in the attached sample file ( original file with data over 100,000 rows)
Column A has many duplicate values and in column S the value is corresponding to this value but its enter only in one cell.
What I want is to copy the value mentioned in column S in front of all corresponding value in column A.
 I hope this clear
LIST-1.xlsx
0
Hi,

I want to create a macro in word which will find words listed in excel and replace the words in MS Word Document.
For example - In my excel the sheet contains 2 columns, 1st column Words_to_Find & 2nd column Words_to_Replace!! The list of words are Abbreviation, (Eg: DBS -Deep brain stimulation) . I want to replace the first instance in the document. Suppose if the full-form is not written in 1st instance the replace it ,
For Eg    [ (DBS) has shown wide applications for treating various disorders in the central nervous system by using high frequency stimulation (HFS) sequences of electrical pulses. ]
In the above line DBS will be replaced by Deep brain stimulation but in the pattern of underline word in the above sentence. It will replace the word with "full-form (abbreviation)".
I am attaching Excel and word document.

Thank you.
0
Amazon Web Services
LVL 12
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

VB Code for Excel

From CUT

Paste VALUES only

Hello Kind Expert!  Need help with VB code that I can apply a keyboard shortcut to so that from a

Cut (Ctrl+x)

the Paste (Ctrl+Shift+Z) pastes values only.

Sincerely,

OT
0
I am looking for a more efficient function,Any suggestions?
=IF($E14="","",IF($E14=0,"",IF($Z14="",$T14/1/$K14,$T14/$Z14/$K14)))
Thanks
0
1- find buttons should be in  OUTPUT sheet
find 2 find 3 find 4

2- SELECTED show results

x111.PNG
x222222.PNG29118087--1-_to_find234_to.xlsm
0
Using Excel keyboard shortcuts

ctrl + semicolon yields the DATE
ctrl + shift + semicolon yields the TIME

both non-dynamic

Need a single keyboard shortcut that will insert

DATE TIME

Both, also non-dynamic

Thanks!

OT
0
select data as show

tehn show in SELECT sheet

rrr1111.PNG29117915_to_make_selected_3n_to_4N.xlsm
0
I have a user with Office 365 Excel 2016 and his Pinned Recent Workbooks inside of Excel keep disappearing.  The non-pinned recent files are not disappearing.   He is not using any file cleanup software (eg CCleaner) and using the Windows 10 Defender for his antivirus.
0

Microsoft Excel

134K

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.