Home

Blogs

VLOOKUP

Submitted by aminxl on 27 December, 2011 - 12:33

Sir/s

I am trying to apply VLOOKUP formula from Anil to 19 (a to b)but it is getting only 71 not 19 but i need 19 how it possible or any other way.

a b

ANIL 71
ANIL 19
BABU 24
BABU 67

VINOD

»

VLOOKUP FORMULA (REQUIREMENT)

Submitted by aminxl on 27 December, 2011 - 12:15

Sir

I have one prob

a b c d

GANGAWATER BOTTLE TIN POCKET
GANGAWATER TIN BOTTLE POCKET
GANGAWATER POCKET TIN BOTTLE (this is master data)

I am trying to apply VLOOKUP a-b VLOOKUP is getting only BOTTLE(b) not for TIN and POCKET i need to pickup TIN how it is possible or any other root is there.

sir any one help me.

VINOD

»

Excel save as .txt

Submitted by kirra on 15 December, 2011 - 05:00

Hi! I need help !!!

I have a spreadsheet in Excel that I'm trying to save as .txt or .csv to be able to import the data into myob.
However, every time I save as .txt or .csv and open it in notepad it doesn't show the data separated by comma, it's separated by tab which it's not the format accepted into myob.

What's happening ?? Am I doing something wrong ?? What should I do ??

I tried to concatenate the data too but it changes the date and numbers format...

»
Vishesh's picture

Text/Number Validation in Text Boxes

Submitted by Vishesh on 11 December, 2011 - 17:19
Copy the following code in a general module and call it from any of the textboxes' keypress event.
»

Tree View in Excel

Submitted by deepakjipaulson on 28 November, 2011 - 14:35

Hi Nick,

Could u pls. assist with the below queries

1. When this file is closed and opened
the form control shrinks & expands- becomes steady only after a couple of scroll

2. When a particular item (parent/child) is selected
the same should get linked to a particular cell in the ctrl sheet

Thanks
Deepak Ji Paulson

»

How to sum hours within a certain time frame within a range of start and end times?

Submitted by GeorgeK7 on 22 November, 2011 - 05:34

Hello,

»

Help with Compile error Next without for

Submitted by ann21236 on 21 November, 2011 - 17:23
I'm trying to get excel to send an email when a command button is pushed. The email needs to be sent out if any date in column E equals today's date. I keep getting a compile error Next without for.
»

Excel Help - VBA

Submitted by cheetahs10 on 15 November, 2011 - 08:21

Help please !!! I have a excel work book with the following sheets , E-1 and Overdue.

The E-1 sheet consist of over a 1000 rows. I have a lot of formulas on that sheet to make certain items in colour and so on.

I need a formula or VBA help to get only the overdue orders on the E-1 sheet and copy them to the overdue sheet.

If the column AC is between -1 and -1000 it should copy that row to the overdue sheet.

If this formula is possible can we put in that column AC should be sorted from Highest to lowest.

»
Vishesh's picture

Solution-Copy data range to new worksheet

Submitted by Vishesh on 8 November, 2011 - 18:14

Solution for problem posted on url

http://excelexperts.com/copy-range-data-new-worksheet-when-matching-data...

Please see attached sheet. Click on the button provided on the sheet.

»
Vishesh's picture

Customised Progress Bar

Submitted by Vishesh on 29 October, 2011 - 18:23

Download the attached Excel workbook to see how the customised progress bar works. To implement this in your project simply copy the form (frmProgress) and general module (modProgressBar). And then, you just need to call ShowProgress procedure like this 

Call ShowProgress("Task 4", 10)
where "Task 4" is the task name and 10 is percentage completion of the task.

Customised Progress Bar

»
Vishesh's picture

VBA code to handle Access Imports and Query

Submitted by Vishesh on 21 October, 2011 - 11:22
Paste the following code in a general module 
Public g_objConnection As ADODB.Connection
Public Const gc_strDBPath As String = "C:\Test.mdb"
 
Function blnConnectDatabase(strPath As String, strDBPass As String) As Boolean
'    If blnFileExists(strPath) = False Then
'        GoTo ErrH
'        Exit Function
'    End If
    Set g_objConnection = New ADODB.Connection
    On Error GoTo ErrH
    g_objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        strPath & ";Jet OLEDB:Database Password=" & strDBPass & ";"
    On Error GoTo 0
    blnConnectDatabas
»
Vishesh's picture

Solution to Forum Ques

Submitted by Vishesh on 17 September, 2011 - 07:59

Solution for Forum Ques on url:

http://excelexperts.com/vishesh-need-macro-help

»
Vishesh's picture

Rows to column solved

Submitted by Vishesh on 15 September, 2011 - 18:02

Solution for problem asked in url: http://excelexperts.com/moving-data-rows-columns-depending-unique-conten...

Pls c attached sheet. I have tried to solve it using formula only (no macros as it would need more involvement).

»

multiply by 100

Submitted by Carmoi on 7 September, 2011 - 18:29

1576 tourist bed-nights multiplied by 100 liters of water per night

What would the formula look like in the formula bar?

»
Vishesh's picture

Filter/Find in Array

Submitted by Vishesh on 7 August, 2011 - 07:51
The following piece of code can be run from any general module. This code extracts all array elements based on the search criteria. You can also search the entire array element or part of it depending on the 3rd parameter of the function if its set to True or False.
»
Vishesh's picture

Count Array Dimensions

Submitted by Vishesh on 6 August, 2011 - 19:31
The following piece of code gives the number of dimensions an array has. In the TestRun procedure a range is assigned to variant and that variant is passed to GetArrayDimensions function as a parameter. GetArrayDimensions function returns the number of dimensions in an array. If the range has only one cell then it returns 0 as its not an array; if it has more than one cell its an array in which case it returns 2.
»
Vishesh's picture

Create In-Cell Chart in Excel using VBA

Submitted by Vishesh on 5 August, 2011 - 08:26

Paste the following code in a general module and pass the required parameters to create an chart in a cell. This code however works with Excel 2007 onwards.

In-Cell Chart

»
Vishesh's picture

Transpose Back

Submitted by Vishesh on 3 August, 2011 - 09:00
Solution for problem on url http://excelexperts.com/re-arrange-excel-data

Paste the following code in any module and run the Test procedure 

Sub Test()
    Call TransposeBack(Sheet1.Range("A3:D10"), Sheet1.Range("I1"))
End Sub
 
Sub TransposeBack(rngData As Range, rngTarget As Range)
    Dim rngHeader       As Range
    Dim rngId           As Range
    Dim rngCell         As Range
    Dim rngDest         As Range
    Dim lngRept         As Long
 
    Set rngDest = rngTarget.Offset(1)
 
    With rngData
        Set rngHeader = Intersect(.Rows(1), .Rows(1).O
»

Re arrange excel data.

Submitted by prem.winsome on 2 August, 2011 - 20:21

All,

I have to re arrange a huge data which is in 240*2000. iam giving the present data format below and the expected output format too. please please some body help. sos.

Present form of the data:

ID NUMBER 05-07-2010 06-07-2010 07-07-2010
55827 1 5 1
55832 2 12 0
55813 3 100 0
55826 1 0 5
55828 0 252 0
55840 3 20 0
55829 6 20 5

expected output of the data:

ID NUMBER AMOUNT DATE
55827 1 05-07-2010
55832 2 05-07-2010
55813 3 05-07-2010
55826 1 05-07-2010
55828 0 05-07-2010
55840 3 05-07-2010
55829 6 05-07-2010
55827 5 06-07-2010
55832 12 06-07-2010

»
Vishesh's picture

Code generated ComboBox (ActiveX)

Submitted by Vishesh on 1 August, 2011 - 07:11
Manually add a combobox to Activesheet and right click to see its properties.
»
Vishesh's picture

Web Query Solution

Submitted by Vishesh on 29 July, 2011 - 21:23

Solution for problem posted on url:

http://excelexperts.com/automating-pulling-data-website

Please use the attached xl sheet. Change the symbol and click the button to see

»
Vishesh's picture

Copy Picture from Web to Excel

Submitted by Vishesh on 19 July, 2011 - 16:50
The following code pastes the picture from Web to the Excel range. The two parameters required by the function are the URL of the picture and address of target Excel range.
»
Vishesh's picture

Array to speed up (Range vs Array)

Submitted by Vishesh on 11 July, 2011 - 16:57
Below two procedures are different ways of populating a range of cells. The one at the bottom using arrays is the faster one. We can use the same to do processing/calculations in arrays and then put the calculated data back to range to speed up the process.
»
Vishesh's picture

Using Array to refer to multiple sheets

Submitted by Vishesh on 10 July, 2011 - 18:41
The following example code deletes multiple sheets in one go. 
Sub DeleteMultipleSheets()
    Application.DisplayAlerts = False
    With ThisWorkbook.Worksheets(Array("Sheet2", "Sheet3"))
        .Delete
    End With
    Application.DisplayAlerts = True
End Sub
»
Vishesh's picture

Abbreviated Month name

Submitted by Vishesh on 10 July, 2011 - 18:18

DO NOT take first 3 characters from a month name to get an abbreviated month name. This can give you duplicate results in some languages as first 3 characters of two months may be same.
I have encountered this with the Czech language.

So, use the following:

If the month is 1 i.e. January

So, to get abbreviated month name ALWAYS use

Monthname(1, True)

This will always return standard abbreviated month name.

»
Vishesh's picture

Deleting all charts & shapes on a sheet

Submitted by Vishesh on 9 July, 2011 - 21:07
To delete all charts 
sub DeleteAllCharts()
Sheet1.ChartObjects.delete
End Sub
To delete all shapes (this includes charts as well) 
sub DeleteAllShapes()
Sheet1.Shapes.SelectAll
Selection.Delete
End Sub
»
Vishesh's picture

Create Chart from Array values

Submitted by Vishesh on 9 July, 2011 - 20:39
This code creates a chart from Array values only. Explore and modify this code to make more complex charts using array only. 
Sub CreateChartFromArray()
    Dim objCht As ChartObject
    With Sheet1
        Set objCht = .ChartObjects.Add(10, 20, 500, 200)
        objCht.Chart.ChartWizard .Cells(1)
        objCht.Chart.SeriesCollection(1).Values = Array(56, 61, 45, 15, 30, 10)
        objCht.Chart.SeriesCollection(1).XValues = Array("A", "B", "C", "D", "E", "F")
    End With
    Set objCht = Nothing
End Sub
»
Vishesh's picture

Export Excel Range to Word Document in a Tabular Format

Submitted by Vishesh on 9 July, 2011 - 20:08
Paste the following piece of code in a general module and pass the range as a parameter.
»
Vishesh's picture

Zoom Select Range

Submitted by Vishesh on 9 July, 2011 - 19:59
This small piece of code can be very useful to keep the viewing area of a sheet across various screen resolutions when you deliver your project to your client. 
Sub ZoomRange(rngZoom As Range)
    Application.Goto rngZoom
    ActiveWindow.Zoom = True
End Sub
 
Sub Test()
    Call ZoomRange(Sheet1.Range("A1:L23"))
End Sub
»
Vishesh's picture

Regular Expression to validate Email address

Submitted by Vishesh on 9 July, 2011 - 19:36
Pass a string as a parameter to the below procedure to check whether the string is a valid email id or not. 
Public Function blnEmailValid(ByVal strEmailAdd As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
        blnEmailValid = .Test(strEmailAdd)
    End With
End Function
 
Sub Test()
    MsgBox blnEmailValid("abc@EE.com")
End Sub
»
Syndicate content