Microsoft Office

69K

Solutions

42K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

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

Sign up to Post

Hello the experts,

byundt has been kind enough to help me improve several VBA codes

He suggests each time to use "ActiveCell" rather than "Selection" but also tells that if I got empty cells, then "ActiveCell" won't work and it's better to use "Selection"

As I often have empty cells in my files I'm tempted to always choose the "Selection" option

I would just like to know why "ActiveCell" is better and what are the risks with "Selection" ?

Thank you very much for your help,

Mélanie
0
Become a Microsoft Certified Solutions Expert
LVL 19
Become a Microsoft Certified Solutions Expert

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).

Hi,
in my Excle sheet
Which is happen first according StockMarketTime
The highest score in (highScore column) OR the lowest score in (lowScore column).
In my example, the equation should return with the row 7, of StockMarketTime = 12:00 and the highScore = 19, because it happen before the lowest score at row 11,  StockMarketTime = 02:00 and the lowScore= 12
So the comparison should always happen in between these two results only (the highest score in column StockMarketTime, and the lowest score in column  lowScore)
I need the result to highlight the row that match my needs.
please help.
there is attached Excel file
stockMarketsstock-market.xlsx
0
I am creating a report using VBA, every week it runs the report for the previous month and current month, so every week the number of rows increases.

I have a sheet that has a couple of graphs, these I created manually.  I want to only show rows in graph that has data, so I would like it to be Dynamic.

For example first week of the month the data may only have 26 rows, but by the end of the month it could have 40 -  If i choose A2:A40 the graph look strange as it is half empty with data and the other half empty.  Is there a way to not show if the data is empty of to add the data to the graph dynamically so if there is 26 rows it shows 26, but if there is 35 rows it shows 35 ?
0
Hi Experts, in Google sheets the filter function/formula is easy to use. Am using it for months now.

I was expecting the same in Excel. However, the Excel that we are using doesn't has the filter function. The function we found with filter in it's name is FilterXML.

Excel is being used for a remote production area. So no access to internet.
What formula alternatives are there? Or how can I find a formula like filter in Excel?
0
This morning I updated a laptop in the office to Win 10 Pro opting to keep all of my apps and data.

I've had a 32bit version of Office Professional 2013 installed on this machine for years.  After the conversion to Win 10 Pro this morning none of my Office 2013 products will open.

They all have the same behavour when trying to open.  The application loads, the open splash screen (for example the blue 'Word' screen) will appear and the application will quickly close with no error message or intervention on my part.

I had another issue on this machine and one EE responder warned me warned me that it might be caused by using the administrator account.  If that's the issue how can I resolve it.  I've only ever had one user set up on the office machines since I am a solo developer.

How can I resolve this issue.
0
I need help with data cleaning. I get these excel sheets (sorry unable to share) one column will have multiple entries in it like 11111-EXAM, 2222- EXAM, 333-EXAM so I do a text to column to break it out but then I need to put it back int o a singular column, so transpose again. The issue is the adjacent columns, I need to fill down the rows with the information that corresponds to that entry.

Rows look like this:
11111-EXAM, 2222- EXAM, 333-EXAM  |   example entry | more information | etc.

and I need it to look like this without the pain of having to copy and paste over and over:
11111-EXAM | example entry | more information | etc.
2222- EXAM | example entry | more information | etc.
333-EXAM | example entry | more information | etc.
0
I am developer, upgrading my Office computers to Win 10 Pro.  I ran the install opting to retain my apps and data.  One of the most important things I do is remoting into clients system for problem resolution installing updates, etc..

I installed Win 10 Pro on one of the laptops in my office.  I am trying to remote in to one of my clientsusing a shortcut retained during the install.

I see the first screen 'C'itrix Netscaler Gateway' and enter my user id and password.  Prior to WIn 10,  after entering my logon info I would get a push to my iPhone from DUO.  That doesn't happen now.  Instead I get the message 'We're sorry access is not allowed with Edge due to security concerns."  I can see the DUO screen disabled in the background behind the message.

What do I need to do to get this to work.
0
Hello hello,

I think I shouldnt have "marked as solution" as a thanks for your help on this topic
https://www.experts-exchange.com/questions/29166939/Check-VBA-Macro-3.html

... as I couldnt add my file to answer Martin question

And this question interests me because I think Martin has something interesting regarding my regional settings and so on.

So, I'll remind my both codes and attach a picture of a typical result. If you see any way to standardize this so that it will work on most machines and most settings to avoid headaches to get our formats rights, it will be very welcome !

Format-Macros.png
Sub TextToNumbers()
    '
    ' ToNumbers Macro
    ' Converts the selected column to numbers (when text stored as numbers)
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.NumberFormat = General
        .Value = .Value
    End With
End Sub

Open in new window


Sub ToNumbers()
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                          ReplaceFormat:=False
    End With
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With
End Sub

Open in new window

0
I am trying to loop through a spreadsheet and copy every 5th row of a sheet into multiple new worksheets. Basically every 5 rows of data should be placed into a new worksheet. The code I am trying to modify to suit my purposes is

Option Explicit

Sub loopTest()
Dim hdr As Range 'header range
Dim dta As Range 'data range
Dim cl As Integer 'copy line
Dim ns As Excel.Worksheet

Set hdr = Excel.Worksheets(1).Range("A2:Q2") 'set this range for what ever range your headers are on
cl = 3 'set this value for what ever row your data starts on
Do While Excel.Worksheets(1).Range(Cells(cl, 1), Cells(cl, 1)).Value <> "" 'this stops the loop when there are no more records
Set dta = Excel.Worksheets(1).Range(Cells(cl, 1), Cells(cl + 2, 17)) 'this sets the data range change the number 3 to how ever many columns there are in your dataset
Set ns = Excel.Worksheets.Add(, ActiveSheet) 'this sets the new sheet to the ns (new sheet = ns ) variable
hdr.Copy
ns.Range("A1").PasteSpecial xlPasteAll
dta.Copy
ns.Range("A2").PasteSpecial xlPasteAll
cl = cl + 5
Loop

End Sub

Open in new window


I currently get an error on the line:
Do While Excel.Worksheets(1).Range(Cells(cl, 1), Cells(cl, 1)).Value <> "" 'this stops the loop when there are no more records

Open in new window


The error I am receiving is Application-defined or object-defined error. I am unsure how to interpret this error.

testworkbook with the code and error is attached.
0
(And final for now as I dont want you to hate me for bothering you too much today ^^)

Hello the experts,

I'm not good with VBA but I use it a lot, I usually record some stuff then ask my friend  google about what I can't get recorded. I know all my macros are probably very weak but as long as I'm the only one at risk it's not an issue. But this time some colleagues are interested and I don't want to "sell" them a rotten file. So... Would you please have a look on my macros and let me know if you see something that should be improved so I can share my file with good chances it will work for other people ? Your corrections are obviously welcome, but also your advices to help me understand why I should do this and not do that...

Thank you very much for your help, Kind regards,

Mélanie

MACRO 1 : https://www.experts-exchange.com/questions/29166936/Check-VBA-macro-1.html
MACRO 2 : https://www.experts-exchange.com/questions/29166937/Check-VBA-Macro-2.html
MACRO 3 : https://www.experts-exchange.com/questions/29166939/Check-VBA-Macro-3.html

MACRO 4 : this is the specific macro for this specific file, basically it deletes this column, format this other one with help from the previous macros, and gets a lot of boring stuff does in a few secondes. It tooked 2mn to run when I did it first, then I improved it with some help here and there... And now it does the job in 3-5s. I dont copy it all, just the structure and a few examples so you can tell me what I should pay …
0
Introduction to R
LVL 19
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Hello the experts,

I'm not good with VBA but I use it a lot, I usually record some stuff then ask my friend  google about what I can't get recorded. I know all my macros are probably very weak but as long as I'm the only one at risk it's not an issue. But this time some colleagues are interested and I don't want to "sell" them a rotten file. So... Would you please have a look on my macros and let me know if you see something that should be improved so I can share my file with good chances it will work for other people ? Your corrections are obviously welcome, but also your advices to help me understand why I should do this and not do that...

Thank you very much for your help, Kind regards,

Mélanie


MACRO 3 : This one is used only on my expanses files, to get the numbers on the desired format (Accounting, 2 decimals). There's a lot of "try & fail" behind what I suppose should be very simple and easy. Note that I'm french and for whatever insane reason we use a comma instead of a dot before the decimals. So I start by eliminating the damn commas and replace them by dots, then I apply the desired formatting. I get a feeling this macro is very wrong. But so far it does the job for me...

Sub ToNumbers()
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
                          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                          

Open in new window

0
Hello the experts,

I'm not good with VBA but I use it a lot, I usually record some stuff then ask my friend  google about what I can't get recorded. I know all my macros are probably very weak but as long as I'm the only one at risk it's not an issue. But this time some colleagues are interested and I don't want to "sell" them a rotten file. So... Would you please have a look on my macros and let me know if you see something that should be improved so I can share my file with good chances it will work for other people ? Your corrections are obviously welcome, but also your advices to help me understand why I should do this and not do that...

Thank you very much for your help, Kind regards,

Mélanie



MACRO 1 - I use this one A LOT, it's stored in my personal.XLSB for the numerous times I have to deal with french dates read as text and want to transform them into actual dates. I use the macro "on the go" or I use it within other macros.

Sub FormatDate()
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range(Selection, Selection.End(xlDown)), DataType:=xlDelimited, _
                            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                            :=Array(1, 4), TrailingMinusNumbers:=True
    Selection.NumberFormat = "[$-en-US]d-mmm-yy;@"
End Sub

Open in new window

0
Hello,

Following this topic : https://www.experts-exchange.com/questions/29166890/Help-on-VBA-request.html

My request was perfectly answered by both of the proposals

I've chosen to use the second one as it's easier for me to understand, only problem is that sometimes I will have to use the macro when there are already numbers in column H

My fault as I first said column H was empty when it actually wont always be empty

Could you please help me change the macro so that it will not touch column H if there are already numbers within ?

Thank you very much for your help & kind regards,

Mélanie
0
I want to count how many occurrences of a certain character exist in a column of Excel data.
For example, if I want to count Ampersands (&) and I have this:
        A
Cars & Trucks & Engines
Cars & Trucks
I want to end up with this
        A                                               B
Cars & Trucks & Engines              2
Cars & Trucks                                  1

Is there a formula (or another method) that can do this?
0
Hello the experts,

Im working on my Travel expanses workflow and was able to get pretty much all I needed done with VBA, except this part where your help will be very welcome, pretty please :)

Suppose columns F, G, H

Column F is full of "C" or "D"
Column G is full of numbers
Column H is empty

I want that IF it's a "C" in F, then the number in G is moved from G to H, for all the column (no more than 500 rows max, different number of rows each time)

Thank you very much for your help !

Kind regards,

Mélanie
macro-should-do-this.png
0
I have a service account called operator In our active directory

But doesn’t see under active users in office 365

I need to create mailbox and assign license

How can I do
0
Excel takes long time in opening in office 365 than it is used to open in office 2010

Anything I should check
0
Publisher cannot be verified, word 2016.   How do I remove this.
I am creating a task sequence in MDT, and when I install an office add-in, and startup Excel/Word for the first time.   I get:  Microsoft Office Customization Installer > Publisher cannot be verified.  

How can I remove this when Word/Excel starts up.  

Thanks Certificate
0
Would you advise me how to pull a data from cells to fomula (red and blue square)?
What does the last green square do in formula?

Snap5.png
0
CompTIA Network+
LVL 19
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

I currently have a MS Office Standard 2016 volume licensed installation on my Windows 10 Pro (1909-18363-347) computer and I need to install a click-to-run version of Microsoft Project Professional 2019. It comes up with an error (see attached image) and won't let me continue. How can I work around this issue so I can run both Office and Project? Can I uninstall the MSI version of Office, install the click-to-run Project and then re-install the MSI standalone Office Standard 2016? Any help would be appreciated.
Thank you.

Installation error
0
In Outlook 2016/365, mail gets stuck in the outbox on one particular computer. This does work fine if we send mail from this account a different computer. Sometimes closing Outlook causes it to be sent but lately that doesn't work either. I delete the PST file and restarted Outlook so got rebuilt. That worked for about 1 day.

Any ideas would be appreciated.
0
Have a column with a drop down menu where is the information coming from?  How do I find the table that is populating the drop down?
0
How to limit a domain account in hybrid office 365 deployment to only office 365 logon.    No local domain access or logon privileges.

User existed already and used to have local domain privileges and logon rights.

User now is remote and permanently only requires access to his office 365 email.      We want to remove all domain privileges and logon rights.      Is there an easy way to accomplish this as some rights may be explicit.

For example can we deny all logons accept office 365?
0
I have a spreadsheet that consists of five worksheets, each with their own individual name.  Each of these worksheets has a "Status" column on them which consists of several status' values in it's filter drop-down, e.g. "not managed", "ping failed", "shutdown" and so on...  What I'd like to do is provide a count of the status (depending on which status you choose) on another worksheet within that same workbook which is named "Legend".  What would be the best formula for attempting this?
test.xlsx
0
I have a client where it appears every few days their server creates an exact copy of it's drive on the office computers. So, for example, the server's drive everybody uses is S:  then after a few days the server will make a copy of it and the users will see an S: and an F:. Both are exact copies of each other. Both drives have all the files and the files have no issues opening. When saving in the usual S: the file instantly is also copied to the F:. Deleting the F: has no impact on the S: and the main drive continues to operate as normal until a few days later then we'll see a G: that is a copy of the S: again.

Any thoughts?
0

Microsoft Office

69K

Solutions

42K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.