Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Microsoft Excel

131K

Solutions

37K

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 am trying to insert blank rows between every row of my data; will you help me with vba code to automate this process?

Thanks in advance

Kathryn
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

How to find them?
I know how to fix the problem, I just can't find em. When I filtered and hid a column it happened.
0
Hello Experts,

I am looking for VBA which does exactly as Find & Replace in Excel.
After replacement it should show me how many search words been replaced.

Any help would be appreciated

Thanks in advance
0
Hello:

In SQL, I created a Windows Authenticated login for myself.  I gave that login access to only one SQL database.

Yet, when I use Microsoft Query in Excel, I get a login error message when trying to access this database.  The only way "around this" is to check the box for "Use Trusted Connection".  Why is that?

Secondly, even when using this checkbox, I get access to all of the SQL databases in the instance--not just the one database that I gave myself access to.  Why?

In any case, my Team and I ultimately used a SQL authenticated-login for Excel's Microsoft Query.  But, doesn't doing so represent a security loophole?

Thanks!

John
0
Hi ,

I need help on this macro.

When the user select the range (student ID) then click on the Button (Macro), Macro need to update the total,avg, result based on the column values..

Is it possible to  create the drop down to select the range of student ID...




Praveen
0
I have a list of names and email addresses with the following format. How can I separate the names from the emails?

What I have:
Column A
Smith, Sue <smithsss@yahoo.com>

What I want:
Column B                       Column C
Smith, Sue                <smithsss@yahoo.com>
0
Hi All,
I am in need of your assistance with Excel.

I have an Excel spreadsheet with two worksheets, Shee1 and Sheet2

In Sheet1, I have the following columns
System
Table
Field
Field Mapped
Table Mapped

In Sheet2, I have the following columns
System
Table
Field

The differences between the two sheets is Field Mapped and Table Mapped in Sheet1

How could I populate Sheet 1.Table Mapped with text Y where
Sheet1.Table
match
Sheet2.Table  

Could this be done with a formula please? I am afraid I do not have access to VBA or to run macros due company policy.

This question is very similar to the question below
https://www.experts-exchange.com/questions/29057789/Columns-matches-from-two-seperate-worksheets.html

Many Thanks.
0
Hi ,
Please help me to create excel marco for attached excel sheet.
test.xlsx
0
Excel Find Distinct Values within Column and sum another column per distinct value
0
Hi,

I am getting the below error while running a .bat file which is using a .vb script.

C:\Windows\System32\Daily Channel Inquiry Report.vbs(103, 1) Microsoft Excel: Sa
veAs method of Workbook class failed.

on 103 line I have this method - objExcel.ActiveWorkbook.SaveAs strExcelPath.

Please help me to fix this issue ASAP.

Thanks
Venkatesh.
0
Free Tool: Subnet Calculator
LVL 10
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Count the number each item appears in a selected column and produce those values in a new tab.

Column A of New Tab = Original Value (text or number)
Column B of New Tab = Number of Times Value Appeared

Assistance is greatly appreciated.  A sheet might contain as many as 15,000 rows.
0
I have previously received great assistance on the attached workbook.  I would now like to know how to make the scrolling window dynamic so that it adjusts as new rows are added to the end of the database and stops at the last row with data.

Thank you for your assistance.
NCCU---Contracts-Inventory-Listing-.xlsm
0
Not sure the best way to get what I need.

I would like total of hours for dates in the past in column D

I would like total of hours for dates in the future (including current day) in column E

See attachment
Planning.xlsx
0
I would like to have the following formula show the totals if the date is in the past

=SUM(APJ6:APU6)     /    total for dates in the past

Also show the total it the date is in the future.

=SUM(APJ6:APU6)   /   total for current day and future dates
0
Hi All,

I am in need of your assistance with Excel.

I have an Excel spreadsheet with two worksheets, Shee1 and Sheet2

In Sheet1, I have the following columns
System
Table
Field
Mapped?

In Sheet2, I have the following columns
System
Table
Field

The same columns in both sheets except for the mapped column in sheet1.

How could I populate Sheet 1.Mapped with text Y where
Sheet1.Table  and Sheet1.Field
match
Sheet2.Table and  Sheet2.Field  

Could this be done with a formula please? I am afraid I do not have access to VBA or to run macros due company policy.


Many Thanks.
0
I am trying to get the function of Application.SumIf() and Application.CountIf() for an internal VBA array defined as Variant.

It is fairly easy to achieve if the criteria Crit in the (non-working) Application.CountIf(tmpArr, Crit) is just supposed to be Equal to the values in the array, but I need Crit to be able to contain criterias like >10, <5 and ideally even wildcards.

Below is a code snippet showing what I have tried for CountIf() , the error it produces and also how I hoped it would work.

Sub CountIfTest()
Dim tmpArr As Variant, Crit As String, tmp As Variant, i As Long
    Crit = InputBox("Criteria", , "<6")
    tmpArr = Array(5, 3, 12, 4, 0, 4, 3, 2, 1)
        
'    MsgBox Application.CountIf(tmpArr, Crit) 'Gives error "Object required" since tmpArr is not a Worksheet.Range()
    For Each tmp In tmpArr
        If tmp Like Crit Then i = i + 1         '"Like" doesn't work, but "If tmp = Crit" works
    Next tmp
    MsgBox "Like test " & i
    MsgBox "Filter test " & UBound(Filter(tmpArr, Crit, True, 0)) + 1                   'Works for Equal, but not for < or > or wildcards
    MsgBox "Match test " & Application.Count(Application.Match(tmpArr, Array(Crit), 0)) 'Works for Equal, but not for < or > or wildcards
End Sub

Open in new window


Any creative ideas?

Thanks,
Jörgen
1
column D has phone numbers in the following format

              D
123.456.6789

I would like to have the phone numbers moved to column K with the following format

            K
(123) 456-6789
0
Is there a way to offset from a dynamic named range to use the same number of rows as the dynamic named range does?
Dynamic.xlsx
0
I had this question after viewing Excel 2010 disable a cell based on another cell.

I am using this to so that if one cell is populated then another is not updatable. Is it possible to have another change option in a worksheet?

Option Explicit

Const PW As String = "123"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
'The code will be triggered if the cell being changed is in column D, starting from Row10
If Target.Column = 4 And Target.Row > 9 Then

    ActiveSheet.Unprotect Password:=PW
    If Target <> "" Then
        Cells(Target.Row, "E").Locked = True
    Else
        Cells(Target.Row, "E").Locked = False
    End If
    ActiveSheet.Protect Password:=PW
End If
End Sub
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I have an excel spreadsheet that if something is selected in a dropdown in cell D10 then E11 will be disabled
0
I am sure this is a simple fix, but I cannot figure out how to get the scrolling window in the attached worksheet titled - Dashboard -  of the attached workbook to stop when it reaches the bottom of the referenced range.  Please see the attached screenshot also.

Thank you for your assistance!
Screenshot.docx
NCCU---Contracts-Inventory-Listing-.xlsm
0
I have recently created a quotation tool which opens up into outlook when the quote is built as a screenshot to send to a customer. I have tested it on my system and it works perfectly, yet I have sent it to a colleague who uses windows 10 (im on 8) and Office 2016 (im on 2013) and it does not work. It just says the location inst available. Please can someone help me with this. I have attached the excel document created.
Quotation-test.xlsm
0
I am working on a project which is Excel based but part of which links to another internal system, a Purchase Order creation platform.

I want to be able to open the PO Platform from the Excel sheet, ideally without going to the Intranet home page first.

Our Intranet home page has a button which links to the PO Platform and I can copy the link from it.

When I use that as a hyperlink in Excel it does not behave the same as when clicking it direct from the Intranet.

Using the link on the Intranet just takes you to the PO platform with single sign on authentication whereas using the hyperlink in Excel shows an error saying user hasn't logged off and then goes to the login window for the platform.  I have spoken to our IT department and apparently it is to do with multiple logins for the PO platform being disabled and we do not want to re-enable it; they have tried in the past and it caused other issues.

So, I am thinking I can maybe set a macro that runs when the hyperlink is clicked. The macro would navigate to the Intranet home screen and "click" the button. The button is stored within a table on the home screen and has a specific jpg as a symbol.

Can this be done?

Thanks
Rob Henson
0
eThe below code is meant to split row data that contains a special character "/" within the cell data for example "SEL/EHL" and populate single value into the row below by having row only for "SEL" and a duplicated row with the cell now with the "EHL" value and to remove the "/" from the metadata that was split.

However the code below for "&" works fine but the above fails to run and generates a run time 9 error message "Subscript out of range" message.

Can any of my peers advise why its happening on this "/" piece of code and not the "&" part of the code even though they are meant to be doing the same thing.

Option Explicit

Sub clean_pos_data()
    Dim rawData() As Variant
    Dim startRange As Range
    Dim v As Long
    Dim i As Long
    Dim j As Long
    Dim x As Long
    Dim cleanData() As Variant
    Dim splitField() As String
    
    With ws_PosSeq
        Set startRange = .Range("a3")
        rawData = startRange.Resize(.UsedRange.Rows.count - startRange.Row, .UsedRange.Columns.count).Value
    End With
    
    For i = 1 To UBound(rawData, 1)
        If InStr(1, rawData(i, 9), "&") > 0 Then
            splitField = Split(CStr(rawData(i, 9)), "&")
            For x = 0 To UBound(splitField)
                 v = v + 1
            Next x
        Else
            If InStr(1, rawData(i, 9), "&") > 0 Then
                splitField = Split(CStr(rawData(i, 9)), "&")
                For x = 0 To UBound(splitField)
                    v = v + 1
             

Open in new window

0
Dear Respectable Experts,

i need help below code attached function Extract a number from a string value and returns numbers as string i need that it returns number as value or number please help me if it is possible.

Thanks.

Option Explicit

Public Function ExtractNumbers(AValue As Variant) As String
 
  Dim Character As String
  Dim Index As Long
  Dim Result As String
  Dim Value As String
  
  Value = CStr(AValue)
  For Index = 1 To Len(Value)
    Character = Mid(Value, Index, 1)
    If IsNumeric(Character) Then
      Result = Result & Character
    End If
  Next Index

  ExtractNumbers = Result
 
End Function

Open in new window

0

Microsoft Excel

131K

Solutions

37K

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.