Microsoft Office

66K

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

I need help with a function that will filter a two dimensional VBA array based on some criteria and store the filtered elements into a new array.
What I need would be clear from the code included.
As you would note I am running a loop to filter a startingArray and storing the matching elements in filteredArray.
The code I have posted works but in this case I was able to ReDim filteredArray(1 To 3, 1 To 3) As Variant because I knew how many elements I would get in the filtered array (in this case 3). In a real scenario I would not be able to do this because I would not know in advance in how many elements in my starting array would match the filtering criteria.
I would appreciate your help in the correct way of doing this.

Private Sub FilterTwoDimensionalArray()

Dim startingArray() As Variant
ReDim startingArray(1 To 6, 1 To 3)

Dim filteredArray() As Variant
ReDim filteredArray(1 To 3, 1 To 3) As Variant
' In the above line I am able to Redim filteredArray to (1 To 3, 1 To 3)
' because I know in advance how many elements filteredArray will need to accomodate
' as I can see the data


startingArray(1, 1) = 1
startingArray(1, 2) = 3
startingArray(1, 3) = "This is an apple."

startingArray(2, 1) = 4
startingArray(2, 2) = 9
startingArray(2, 3) = "He looked happy."

startingArray(3, 1) = 10
startingArray(3, 2) = 12
startingArray(3, 3) = "This is an apple."

startingArray(4, 1) = 13
startingArray(4, 2) = 16
startingArray(4, 3) = "This is a good 

Open in new window

0
Price Your IT Services for Profit
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Dear Experts:

I got an .xlsm file which only should be saved as .xlsm file by the user.

Is it possible to restrict the user to just save it as .xlsm-file and not as an .xls or .xlsx or other file type? And If yes, how would this VBA code look like?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Hello Experts,
I am trying to documenting my various procedures. Could you please advice me with the following questions:

  1. Best way to loop used range in a specific sheet in order to apply same height
  2. Best way to loop used range of all sheets in workbook.
  3. Best way to loop columns related to usedrange in activesheet in order to apply same width

Thank you very much for your help.
0
Dear Experts:

I got a form with Active X Control boxes.

In the below code only Range("F23") is checked whether it contains the boolean value 'False'.

I would like to expand the IF statement so that Range "F23" to Range "F30" is included, i.e. the IF statement is true
 if any number of Cells (1 to 8) in the range "F23" to "F30" has the boolean value of 'False'.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas

Sub Check_for_value_in_a_range()

If ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = True And ActiveSheet.Range("F23").Value = False Then
ActiveSheet.OLEObjects("chbx_GenSurg").Object.Value = False

End If

End Sub

Open in new window

0
Gurus ,

Good day to you . If i have two sheets , do i have an option to see the discrepancies between the sheets ( eg : description , qty , value etc )

Regards,
Sid
Sim1.xlsx
Sim1-v1.1.xlsx
0
running access 2016 as part of office 365. Now trying to run an existing access app that requires ms office xx.x object library and its no where to be found in references list.
How do I get an object library?
0
Is it possible to have AAD Connect automatically synchronize to Office 365 whenever a new account is created automatically on-premise without running the AD Sync manually?
0
Hi,

I can't find the Microsoft Office folder under Programs (x86).  Any ideas why this is missing?  I'm able to use the Microsoft applications just fine but in order to install a vendor add-in correctly, they said this folder needs to be present.
0
Hello Experts - I'm thinking of hiring a part time helpdesk person to assist me with some basic IT stuff like installing software, answering helpdesk calls etc.  I'm not sure what hourly compensation would be fair for someone who is relatively inexperienced, maybe just out of school, and doing this maybe 20 hours per week.  This would be in an architectural office near Trenton NJ.  Would appreciate any feedback, thanks!
0
I need a working example to extract an i address from a string using regular expressions.
Thanks
0
Why Diversity in Tech Matters
LVL 13
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

For at least 5 years I have often used Windows snipping tool to copy a range of cells to the clipboard., then paste the picture into Excel.

But today I ran across some old notes which reminded me that Excel 2003 had  a similar function that was even easier.

I cannot remember the details, but it was something like this.

Copy cells to clipboard.
Hold down shift key and use the Insert Picture command.

Unfortunately, I can't remember the exact sequence, and it might have involved  the  Ctrl key or Alt key. and perhaps it was the context menu?  

Does anybody know of  a similar trick in any version of Excel ?

Or perhaps I am just remembering something completely different (in which case just ignore this question. ( Please do not propose an answer like "no such trick exists:")
1
Hello,

I am looking for a procedure in order to  
Open file attached located at: Application.StartupPath
If the file doesn’t exist exit sub with the following message “File: File.name doesn’t exist”
Else continue and transfer sheet “1-“ to active workbook and rename it with current date: YYYYMMDDMMSS. Continue procedure with the following message “Do you want to remove the various sheets except sheet already transferred. If Yes remove them else keep them.

If you have questions, please contact me.
1-Table-template.xlsx
0
Hybrid Office 365 Environment, Exchange 2010

Quick query on users who share a mailbox together, when one of them makes a change to contacts, the others want to see the changes is there a way for when users sharing a mailbox that they all can see the updated contact list
0
I have a column of products and their cost.  
I have another column of names of people.  
Am wondering if there a way that I can calculate how much “cost” a person is based on what products are attributed to them?  
So a chicken is $5, a cup is $2.  
Sally is signed up for a chicken and a cup so their total is $7.    
Would I have a column of names and products? Or vice verse, and a separate vlookup or something based on another sheet that has a corresponding amount per each product/item?
I’m sure I’m overthinking this or under explaining but I am awful with Excel.
0
I have a windows 10 system that I installed Office 2007 SBE on. after some issues we had to wipe the system and reinstall completely. After reinstall of the OS and Office everything seems to be ok but I can't create Signatures or search at all. I have tried rebuilding the Index and registry keys but nothing is working. The only thing that I can think could be the issue is that the person using the system has a HUGE PST (17GB) I have not tried to run repair on it but that will be my next action but don't think that it will work. does Anyone have any options for me?
0
I have the following VB command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "q_testquery", "strFilePathName", True

Is there a way if "q_testquery" returns no records still have an excel document generated with "No Records Found"?
0
we have on premise AD and ADFS server and we have mailboxes in office 365

we dont have exchange on premise, one user for 5 mins could not create any user groups in his skype or add users

can there be any reason , as of now he can add

we have office 365 proplus installed, is it due to ldap conenctivity to AD or

not sure how skype for business works
0
Hello experts,

I have the following attached table template. When I insert rows in between I have an error message related to formula reported in column A.
The idea is to display automatically the row number even if I insert rows in between.
What is the best way to proceed?
Thank you in advance for your help.
1-Table-template.xlsm
0
Excel - I need to protect a workbook from edits but still allow people to view and filter. What is the easiest way to do this.  I tried Protect but them they can't filter.
0
Build an E-Commerce Site with Angular 5
LVL 13
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Hi

i would like to know , with the example mentioned below .
is it possible for cell b5 , instead of click dots , can i have pull down menu to show

in short i would like to know two questions

1. How was the click system configured ? ie , if i click CAT1 , was there a macro that said to go to specific cells in DB , or was the formula controlling it on B8
2. As i have more items , can i have instead of dot selection, i can use pull down menu , so corresponding fields appear. in this case, how can i achieve it ?

Thanks ,
Sid
TEST0Button.xlsx
0
Dear Gurus,

Lets say , i  add date in excel sheet and i would like to know the counter of days passed , when i access it after couple of days

In short ,

I have date assigned : 14 Mar 19

When i check  my sheet random , eg : 17 Mar 19   , i should show in  cell x # of days passed ( and in different color to identify easily )

is this possible in  excel

BR ,
Sid
0
Hi Gurus,

Is there a way i can timers set in excel
for eg :

1.  i will have two columns ie start date and end date , i want in third column to tell me with the current date on how many days left
2.so if i reach threshold lets say ' three days left' the cell should change color to red or any other color .

Reason is to monitor the tasks assigned to my colleagues and to follow up priorities based on nearest deadline dates


Thanks,
Sid
0
In my excel spreadsheet 2016 I got this Visual Basic Error when I am trying to pass  date parameters to my store procedure.
Not sure why this is happening.


Run Time Error ‘9’
Subscript out of Range
Private Sub CommandButton1_Click()

Dim SellStartDate As Date  'Declare the SellStartDate as Date
Dim SellEndDate As Date    'Declare the SellEndDate as Date

SellStartDate = Sheets("Sheet1").Range("B3").Value   'Pass value from cell B3 to SellStartDate variable
SellEndDate = Sheets("Sheet1").Range("B4").Value     'Pass value from cell B4 to SellEndDate variable


With ActiveWorkbook.Connections("SP_Connection2")<<<<<< Error line here - solved
 .OLEDBConnection.CommandText = "EXEC Datawarehouse.dbo.sp_Test_Data '" & SellStartDate & "','" & SellEndDate & "'"
 .Refresh <<<< Run Time Error '1004'
End With

End Sub
I solved it it was wrong speling for : "SP_Connction2" vs "SP_Connection_2".
Now I passed it and got  this error :
Run-Time error '1004'
Exception has been thrown by target of an invocation
1
Hi all,

We had a KMS host that was recently decommissioned without us knowing it had the KMS service on it. We then started getting several users complaining about Office 2010 Professional Plus and Windows 7 asking them for license activations, and this has been increasing daily.

That host was a physical server that cannot be brought back up anymore, so we are trying to build a new KMS host.

I'm hoping someone has the experience in doing this as I am not too clear on how to proceed.

- I've set up the KMS host role on a Server 2012 R2 Standard edition
- I've downloaded and installed the KMS host set up for Office 2010 professional plus from here: https://www.microsoft.com/en-us/download/confirmation.aspx?id=25095
- Once installed, this is where I ran into some confusion. It asked me for a host key, but my volume licensing center doesn't have anything under "Office 2010 Professional Plus KMS Host Key" - so I put in the KMS key for Office 2010 Professional plus.
- I ran slmg /sdns, then restarted the Software licensing service

My questions are:
- where do I get the Office 2010 Professional Plus KMS host key from, if it's not in the VLSC portal?
- we are having the same issue with Windows 7 licenses. Do I create a seperate KMS server for those or can it be done on the same KMS server?
- is the host key separate from the products I am trying to license?

Any help would be appreciated. Thanks!
0
Need a formula for either happend
the first part already work

=+SI(Y(ABS(AA10)<6,ABS(AD10)<6,ABS(AG10)<6,ABS(AJ10)<6),"DIF MENORES","")  o  SI(Y(ABS(AA10)>9,ABS(AD10)>9,ABS(AG10)>9,ABS(AJ10)>9),"DIF MAYORES","")

TRANSLATION

=+IF(Y(ABS(AA10)<6,ABS(AD10)<6,ABS(AG10)<6,ABS(AJ10)<6),"MINOR DIFERENCES","")  OR  SI(Y(ABS(AA10)>9,ABS(AD10)>9,ABS(AG10)>9,ABS(AJ10)>9),"MAJOR DIFERENCES","")


As i say the first part is working  i need to achive the second part
regards
0

Microsoft Office

66K

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.