Microsoft Excel

137K

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

Hello Experts,
I have some code I need to modify, but when I add my entries it does not play well and throws the copying of cell values incorrectly.

I need to add code that will copy the value of source workbook ("A3") to destination workbook (Column A) while looping through each worksheet.  However the value in ('A3") contains a "#" symbol preceding the value and I want to capture only the remaining Left 3 characters as the value to copy to the destination workbook.
(or copy the ws.name which is the 3 digit character I am seeking to use)

I am posting my code below:

Private Sub GetInfo()
   
    Dim j, k, colOffset As Integer
    Dim i As Long, labelRow As Long
    Dim siteNo, ref, colName, wrkbook As String
    Dim header, target As Range
    Dim Sourcewb As Workbook
       
    For i = 1 To Application.Workbooks.Count
        If Workbooks(i).Name Like "CRG OPERATING PARTNER*" Then
               Set Sourcewb = Workbooks(i)
            Exit For
        End If
    Next i
                     
    If Sourcewb Is Nothing Then
        Beep
            MsgBox "CRG OPERATING PARTNER P&L 3) FINAL" & vbCr & "             EXCEL FILE IS NOT OPEN!"
            Set Sourcewb = Nothing
        Exit Sub
    End If
       
    wrkbook = "[" & CStr(Sourcewb.Name) & "]"
   
    KEYDATA.Unprotect
    Application.EnableEvents = False
   
    Range("N3:AD13").ClearContents
       
    k = 1
   
    Set header = Range("N1")
   
       While …
0
Exploring SharePoint 2016
LVL 19
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

i am using .Open dbConnection.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table")) to get the sheet names in a closed workbook
everything works fine.
once the i retrieve the sheet name can i rename the columns using
  .Open dbConnection.OpenSchema(adSchemaColumns, _
            Array(Empty, Empty, db_table_name))
or do i have to use a different function
0
the attached excel workbook search forms are not working on my home laptop (using windows 10)
- the error msg is "could not load an object because not available on this machine"
- there is 1 only other excel worksheet i need to be added to the attached workbook which i will add once this workbook is fixed
zSearch-Form-Master_19.11.16.xlsm
0
Hello, I need assistance with a Vlookup Macro

Can you please build me two macros.  The first one is just so that I can view how the a VBA lookup would look, the second one is the not show the lookup values but use them to display this dates that fall within the range

1. Please create a vba vlookup to E3 on the Employee Data to pull in OUD1 - OUD9 from the Org Unit Master.  If the Org Unit is not in the list display, "OU Not listed" in column F and blanks for columns G-N.  Results would look like A-N on the Employee Data tab

2. I need a macro to only display Date 1, if it fits between the >=Begin Date and <End Date.  So to display Date2 if it's between Begin Date and End Date.  If the Org Unit is not in the list display, "OU Not listed" in column F
Results would be A-E and Date 1 - Date 8 (not showing the OUD1 - OUD9

The Employee Master can be a shorter or much longer list
VBA-Vlookup-and-date-range-insert.xlsm
0
In column A we have a list of Customer ID numbers (most appear more than once). In Column B we have a list of dates. In column C we want the Clients 1st apptointment. Or the "Earliest" date in the list.
0
Hi Experts,

I'm using the following code to get data from a closed workbook:
Private Sub Workbook_Open()          'This sub must go in ThisWorkbook code pane
Dim frmla As String
frmla = "='C:\Users\" & UserName() & "\Desktop\[filename.xlsx]General'!BJ5"
Worksheets(1).Range("B2").Formula = frmla   'Change cell reference to put formula where you want it
End Sub

Open in new window


Public Function UserName()
    UserName = Environ$("UserName")
End Function

Open in new window


Is there a way to loop through to have this:
B2: ='C:\Users\" & UserName() & "\Desktop\[filename.xlsx]General'!BJ5"
C2: ='C:\Users\" & UserName() & "\Desktop\[filename.xlsx]General'!BJ6"
D2: ='C:\Users\" & UserName() & "\Desktop\[filename.xlsx]General'!BJ7"
...
B3: ='C:\Users\" & UserName() & "\Desktop\[filename.xlsx]General'!BK5"
B4: ='C:\Users\" & UserName() & "\Desktop\[filename.xlsx]General'!BL5"
B5: ='C:\Users\" & UserName() & "\Desktop\[filename.xlsx]General'!BM5"

until

AN59: ='C:\Users\" & UserName() & "\Desktop\[filename.xlsx]General'!DO43"

Thank you.

Michael
0
I am going to do my best to try and describe what I want to do.  I am trying to build a model that uses excess cash to pay down debt but be able to choose which payment based on the interest rate associated with that payment would be the most advantageous to pay down.  If you look at tab Call_2020 it was my attempt to start thinking about how I can do this.  I created a column that calculates the interest cost savings per a $5,000 of principal paid to create a unit that I can measure (the principal can only be paid in $5,000 increments).   Based on this I ranked each of the possibilities so I know which principal amount to start with.  Here is my challenge say if I have $1,300,000 in the first year to pay down my loans I can maybe payoff one full principal that would save me the most and then part of another.  I need a formula that can identify which maturities to pay down and then on the Call Analysis Tool list under the three columns the principal that was not paid to recalculate my loan cost to determine how much free cash flow will be available the next year.   The process would then get repeated each year until all of the loans where repaid.   Essentially how can I continue each year to make my annual payments but also pay down debt quicker with excess revenue.   I have attached the worksheet that I am working with.  I have also included on another tab a simpler version I completed earlier with only one loan (Modified Call Analysis) so you can get an idea of what I am …
0
I have two files
I want to open book1.xlsx and copy filtered data and paste it to book2.xlsx without changing BBB data.
book1.xlsx
Book2.xlsx
0
Looking for the word "complete " using the find function in excel. My results are Cell             VAlue
                    sheet lava                                                                                                         $BF$58   Complete
I go to cell BF58 and nothing is there. Do the dollar sign mean it is referencing another workbook or sheet in the workbook
thanks
0
I want to clear the contents of certain cells in excel based on a a duplicate value in column A but on different rows.
Here is what i was given as a possible solution:
Step C is where I am stuck at.

Create a single query that contains all of the data for the POAM and details tables, sorted by the POAM key and then the Key in the details.
     b.  Use Excel Automation to open excel, write the column headers to the first row
     c.   Use the CopyFromRecordset method to place the data in the rows starting at  A2
     d.   Use Excel automation to loop through the rows in the Excel worksheet, deleting the data in the first 6 columns if the POAM Key of the current row is the same as that of the previous row.
0
Become a CompTIA Certified Healthcare IT Tech
LVL 19
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

I need assistance with VBA.
I need to rewrite the following excel formula into VBA.

=IF(OR(COUNTIF(car$A:$A,color!A2,COUNTIF(car$C:$C,color!A2)),”Yes”,”No”)

I have a workbook with TWO worksheets, cars & color.
the results will be in column N of the “cars” worksheet (starting from N2 to the last row).
0
Experts,

I am looking for a few enhancements to the attached user-form.  I am not that knowledgeable in VBA and do not know how to manipulate the VBA script
1.  I need to add an additional column to the Original and RAW_DATA sheets called Renewal Date and,
2.  I also need to add a new field on the user form called Renewal Date that will auto transfer to both sheets.
2. Based on the renewal date I would like to be able to to  send from excel via outlook a notification  40 days prior to the renewal date expiring.
3. The notification email is only needed for licenses with more than 12 months showing  as stated in the original term column.


https://filedb.experts-exchange.com/incoming/2019/07_w28/1426160/29151783.xlsm
0
Why do I need the word "application" before "statusbar".  Why not just "status bar"?
I don't use "application" in other lines of code in my macro.


Sub test()

Application.StatusBar = "Please be patient..."

End Sub
0
I have VBA code that Protects and unprotects sheets. It’s pretty basic code. ThisWorkbook(Sheet1).unprotect “Password”. In excel 2010 this woks fine.  Note it was Excel 2010/Windows 7

I now have Office365/Excel2013/Win10. I assume it’s Excel 2013 as I am on the road now. For some reason when the unprotect code runs it jumps to the tabs involved in the unprotect. Also if I step thru the code line by line for the unprotect it works. It. Stays in the first tab where the code was called. However if I run the code from the first tab using a button to run the code it moves me to various tabs in the workbook even though the only code running is the unprotect and turning off screen update, etc.

Again it works fine in Excel 2010 for years. Any suggestions.
0
My question is:

I have this path finder code in my macro which is:

LocalAddress = Application.ThisWorkbook.FullName

It is working fine for getting my file but when i pass this code to my team member who works from different computer and drive, it is not working for him.

Please help in this code line so that it can work with any computer.

thank you so much in advance.
0
I had this question after viewing I have code that records audio in excel vba for a set period of time I need help modifying it so that I can turn it off and on when I want.

So this is an old post but applies to something I am currently working on with the same request "how can I turn it off an don when I want" using a button click event.

In reading this post it suggested that the final comment "solved" the issue with the program crashing after 10 seconds, which in running the code it still crashed.  Also the original question of, "how can I start a recording and then stop it when I want" was never really address from what I read.

I have a version of this script shown below where the timer code is "commented" out and the codes does indeed run, for as long as I want, and is "ended" by choosing yes in the msgbox pop-up.  What I am looking for as a solution is to be able to separate the code so I can start the recording with one button event, and then stop it with a separate button event that will both stop the recording and save the file to a specific path.

I am working in an Access 2013 vba application, with a user form for starting and stopping the recording.  Any assistance or suggestions would be appreciated.
Book1.xlsm
0
I would like to import data from a CSV file into a pre-formatted Excel spreadsheet.
The CSV file has 25 fields.  The Excel sheet is made to look like a form.
The CSV file will change daily.
Is there a way to import the data using a script or macro directly into the Excel spreadsheet?

Excel does not have to be used, however even Word or another office program can be used. GeneralForm.xlsxCSV.csv
0
I'd like to create a vba macro that pulls all the available tabs from an 2 existing .xlsx documents into my current project. Each of the 2 xlsx projects have multiple sheets within each. I'd like to combine the data from both and consolidate..

for example

workbook 2019 has 13 tabs
workbook 2018 has 15 tabs

Combine the tabs which have the same name, and don't combine any unique ones. The ones with the same names, should have the same format in terms of rows. Once combined, I'd like to sort by if the row header has the word (Month), then secondary sort by if the row header has (Company or VendorName)

Is someone able to help me kick-start this?

Thanks!
0
What ADO VBA code do I need so that when a user updates Workbook1 (Open) it will instantly update the same cell in workbook2 (Closed) WITHOUT opening workbook 2.

In the past I thought I saw some ADO code for doing this but could not get it to work and would like explore this as well as other alternatives and get your feedback on best method.
0
Exploring SQL Server 2016: Fundamentals
LVL 19
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hello experts,

The following procedure allows me to export the tables through adbod connection.

Sub SQLQuery(wsName As String, strSQL, strInitialRange, strServer, strPort)

Dim objRS
Dim SQL

Set wb = ActiveWorkbook
Set DestSh = Nothing
On Error Resume Next
Set DestSh = Sheets(wsName)
Set wsConfig = Worksheets("Config")
On Error GoTo 0

    If DestSh Is Nothing Then
        Set DestSh = wb.Sheets.Add(After:=Sheets(wb.Sheets.Count))
        DestSh.Name = wsName
        wsConfig.Select
    Else
        Sheets(wsName).Cells.ClearContents
        wsConfig.Select
    End If
    
Set objRS = CreateObject("ADODB.Recordset")


' ==> Connections
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={PostgreSQL Unicode};" & _
"Server=" & strServer & ";" & _
"UID=user;" & _
"PWD=password;" & _
"Port=" & strPort & ";" & _
"Database=db;" & _
"sslmode=require"

SQL = strSQL
objRS.Open SQL, objConn

On Error Resume Next
Set rs = objConn.Execute(SQL)
On Error GoTo 0
If rs Is Nothing Then
MsgBox "SQL query reported at row " & rw & "  is not properly set up unable to transfer  data."
Exit Sub
End If

 For Idx = 1 To rs.Fields.Count
        Sheets(wsName).Range(strInitialRange).Offset(0, Idx - 1) = rs.Fields(Idx - 1).Name
    Next

    Sheets(wsName).Range(strInitialRange).Offset(1).CopyFromRecordset rs

Set objRS = Nothing
Set objConn = Nothing

End Sub

Sub SQLQueryoutConfigSheet()

    Dim wsConfig As Worksheet, wsResult As Worksheet
    Dim UserString
    Set wb = 

Open in new window

0
Is it possible to work (collaborate) in excelsheet that is located on a local server (share) ?

There is no problem using onedrive, but we dont want to have the files in the cloud.
0
Hi. In Excel 2019 I am getting the pop up in the image. When I click on it I am asked if I want to download updates so I choose yes but then it is there  again when I open Excel. How can I stop this.

Thanks
Image1.png
0
Hello experts,

I am looking for a procedure that allows me to define specific values in a ComboBox and based on the value selected send information for range B1, B2, B3, B4, B5.
The idea is to have Dev, Qual, Prod values in the ComoBox and based on this send information for the ranges listed bellow.
I prefer to manage values in an Array instead of defining specific range at worksheet level.
I attached dummy file in which I have already defined UserForm.
20191102_145820-screenshot.pngIf you have questions, please contact me.
Thank you for your help.
UserForm-ComboBox.xlsm
0
I have a sheet that needs simple start stop reset timers built.
When the timer expires if we can have some kind of indicator that would be great. Flashing time, or flashing cell or something.

Oh yah see the 30 minute 45 minute and 90 minute buttons -Thanks

Thanks so much
Chris
 simple timer to be placed into sheet with start stop reset buttonsroom-_1simple.xlsx
0
Hi

we just did a Win-7 / office 2010 upgrade to Win-10 / office 2016 and one user is having an issue that I hope someone maybe familiar with.

when he is in a spreadsheet and calculations are running, they stop when he simply moves his mouse.

he says when he had office 2010, calculations would only stop if he hit the escape key, which of course seems to make more sense.

Does anyone have any insight into why calculations would stop in Excel 2016 simply by moving the mouse...
0