Posts by venkat1926

Re: Function COLUMN INDIRECT in VBA

first portion is a sheet formula
later portions of codes of excel vba

if you want to use vba

suppose A1 has
P4
P is in caps

try this macro

Sub colnumber()
Dim j As Range
Set j = Range("D18")
j.Clear
j.Formula = "=code(left(a1,1))-64"
MsgBox j
End Sub

you will get 16

Re: Macro to save workbook based on cell data from specific worksheet

see whether this macro works

Sub workbooksave()
Dim path As String, ddate As Date, nname As String
path = "e:\excel\"
ddate = Worksheets("Master worksheet").Range("A1")
nname = Day(ddate) & Month(ddate) & Right(Year(ddate), 2)
ActiveWorkbook.SaveAs Filename:=path & nname & ".xlsm"


End Sub

modify

Re: How to compare 2 results among years and among different schools?

that pivot table did not give correct resulte

see attached file with macro in vbeditor. run the macro test and see whether you get what you want

I am repeating the macro here also.

Sub test()
    Dim r As Range, yyear As Range, trial As Range, student As Range, grade As Range
    Dim rfilter As Range, j As Long, rgrade As Range
    'MsgBox Range(Range("L7"), Cells(Rows.Count, "R").End(xlUp)).Address
    Range(Range("L7"), Cells(Rows.Count, Range("R1").Column).End(xlUp)).Cells.Clear




    Set r = Range("A1").CurrentRegion
    'Set yyear = r.Columns("B:B")
    'Set trial = r.Columns("D:D")
    'Set student = r.Columns("E:E")
    'Set grade = r.Columns("F:F")
    r.AdvancedFilter xlFilterCopy, Range("L1:N3"), Range("L7"), True
    Set rfilter = Range("L7").CurrentRegion
    rfilter.Sort key1:=Range("M7"), key2:=Range("O7"), key3:=Range("P7"), Header:=xlYes


    Set rgrade = Range("R8")
    Do
        If rgrade.Offset(0, -1) > rgrade.Offset(1, -1) Then
            rgrade = 1
        Else
            rgrade = 0
        End If
        Set rgrade = rgrade.Offset(2, 0)
        If rgrade.Offset(0, -1) = "" Then Exit Do
    Loop


    rgrade = WorksheetFunction.Sum(Range(rgrade.Offset(-1, 0), Range("R8")))


    rgrade.Interior.ColorIndex = 6




    MsgBox "macro over"






End Sub

Re: How to compare 2 results among years and among different schools?

why not try pivot table

the file is attached. sheet1 is data converted to table

see sheet pivot

the pivot table in A4 to c11
if you select A4 you can see the pivot table field lits

this piovt table is copied and pasted VALUES in G4 to I8
see later formula in column j

Re: Return value between specific times for TODAY() & Yesterday

A1 is having this formula
=TODAY()+3/24
B1 has the formula
=TODAY()-1+6/24

format them as m/d/yy hh:mm (in my computer date is entered as m/d/yy-see control panel-region and language-short date for your computer)

Re: Vba insert values table

presume the data is in two columns A and B from row 1 down

15 15
20 21
18 24
17 18

try this macro and see column E

Sub test()
Dim j As Long
Dim k As Long, dest As Range
For k = 1 To 2
Set dest = Cells(Rows.Count, "E").End(xlUp).Offset(1, 0)
Range(Cells(k, 1), Cells(k, 1).End(xlDown)).Copy dest
Next k
Range(Range("E2"), Range("E2").End(xlDown)).RemoveDuplicates , Header:=xlNo
msgbox "macro done"


End Sub

Re: Seaching selected words using VBA

I have attached the file. check whether macro "test_revised" works ok.
just run "test_revised"
it clears the data from col F onwards to UNDO the result of already run the macro and runs the macro. I think the problem may be my sheet starts from col C .does your real document start from A

if it woks ok what is the difference in configuration of my sheet and your real document.

Re: Combining multiple rows and transferring data to multiple tabs based on row heade

as long as there are no blank rows and columns the code

set r=range("A1").currentregion

will take care of dynamic data

you can even check by using

msgbox r.address

Re: Combining multiple rows and transferring data to multiple tabs based on row heade

autofilter "all products" sheet for unique product type (e.g. HO) and copy filtered data to the corresponding sheet.(sheet HO)
remove autofilter and again filter for the second unique product etc

can you write a macro

Re: Seaching selected words using VBA

Quote

a word from column D can correspond with multiple terms (The word "niet" can be found in every cell in column B blad 2). So I need the corresponding results to apear in the same row starting with column F

I have modified the macro and given the macro new name "test_revised"
Note: your E6 in blad1 is blank. I have inserted x in that cell to avoid that blank

Sub test_revised()
    Dim x As String, cfind As Range, r1 As Range, c1 As Range, summary As Range
    Dim add As String
    Application.ScreenUpdating = False
    With Worksheets("blad1")
    Range(.Range("F1"), .Cells(1, Columns.Count)).EntireColumn.Cells.Clear
    End With
    
    With Worksheets("Blad1")
        Set r1 = Range(.Range("D1"), .Range("D1").End(xlDown))
        For Each c1 In r1
            x = c1
            With Worksheets("Blad2")
                Set cfind = .Columns("B:B").Find(what:=x, lookat:=xlPart)
                add = cfind.Address
                If Not cfind Is Nothing Then
                    Set summary = cfind.Offset(0, -1)
                    'msgbox summary
                Else
                    GoTo nextc1
                End If
            End With
            '.Cells(c1.Row, "F") = summary
            .Cells(c1.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = summary
            'With Worksheets("Blad2")
            
            Do
                With Worksheets("Blad2")


                    Set cfind = .Cells.FindNext(cfind)
                    If cfind.Address = add Then Exit Do
                    If cfind Is Nothing Then Exit Do


                    'If Not cfind Is Nothing Then
                    Set summary = cfind.Offset(0, -1)
                    'msgbox summary
                    'Else
                    'GoTo nextc1
                    'End If
                    End With
                    .Cells(c1.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = summary
                    Loop
nextc1:


            Next c1
        End With
    
    MsgBox "macro done"
    Application.ScreenUpdating = True
End Sub

" it also has to work with selected or idividual cells in column D, and not just the entire column."

instead of looping through D1 to D6 you can remove that FOR -NEXT and use the cell address with a dot e.g. .range("D2"). try modifying the new macro for this and give a new name, save the file and test

after running test_revised Blad1 will be like this

1 lokaal A Spijbelaars
2 donkere C Little Bit Love
3 drugs F Little Bit Love The Drug Lord
4 zin F The Drug Lord
5 wereld A Little Bit Love The Drug Lord
6 niet x Little Bit Love The Drug Lord Spijbelaars

Re: Seaching selected words using VBA

not clear what you mean by this
"the titles from those stories"

I think what you want is for e.g that if "lokkal" from column D of Blad1 is found in column B of Blad 2 you want corresponding term in column A of Blad to be found.
I have given a macro and see column F of blad1

Sub test()
Dim x As String, cfind As Range, r1 As Range, c1 As Range, summary As Range
With Worksheets("Blad1")
Set r1 = Range(.Range("D1"), .Range("D1").End(xlDown))
For Each c1 In r1
x = c1
With Worksheets("Blad2")
Set cfind = .Columns("B:B").Find(what:=x, lookat:=xlPart)
If Not cfind Is Nothing Then
Set summary = cfind.Offset(0, -1)
MsgBox summary
Else
GoTo nextc1
End If
End With
.Cells(c1.Row, "F") = summary
nextc1:


Next c1
End With


End Sub

you can create a command button and assign this macro for that button
I am also attaching the file

Re: linking stock price quotes from external source

excelclout
doe this work for Indian stocks BSE AND NSE

Re: Ranking based on an arbitrary order

some apparent inconsistencies. 8 blocks of data in sheet 1. only 6 cutom lists in sheet 2

for whatever is worth I have prepared a macro "test" I dont know whether the macro gets what you want see attached file.

the macro is also repeated here

Sub test()
Dim sortrangefirst As Range, sortorder As String, j As Integer, sortrange As Range
Dim rfirst As Range, rdata As Range, n As Integer, k As Integer, m As Integer
j = 0
With Worksheets("sheet1")
Set rfirst = .Range("a3")
End With
Do
With Worksheets("sheet2")
Set sortrangefirst = .Cells(1, 1).Offset(0, j)
MsgBox sortrangefirst.Address
If Len(sortrangefirst) = 0 Then Exit Do
End With


With Worksheets("sheet2")


Set sortrange = Range(sortrangefirst, sortrangefirst.End(xlDown))
MsgBox sortrange.Address
'sortorder = sortrange.Value
'Application.AddCustomList (sortorder)
n = Application.CustomListCount
'---------------------------------------
'k = WorksheetFunction.CountA(sortrange)
'MsgBox k
'sortorder = ""
'For m = 1 To k
'sortorder = sortorder & sortrange.Cells(m, 1) & ", "
'MsgBox sortorder
'Next m
'MsgBox sortorder
'sortorder = Left(sortorder, Len(sortorder) - 1)
'Debug.Print sortorder
Application.AddCustomList listarray:=sortrange
'---------------------------




n = Application.CustomListCount


With Worksheets("sheet1")
Set rdata = rfirst.CurrentRegion
MsgBox rdata.Address
rdata.Sort key1:=rfirst.Offset(0, 2), Header:=xlNo, OrderCustom:=Application.CustomListCount + 1


Application.DeleteCustomList n
Set rfirst = rfirst.End(xlDown).End(xlDown)
End With
j = j + 1
If Len(rfirst) = 0 Then Exit Do
End With
Loop


MsgBox "macro done"


End Sub

Re: excel sorting by custom list

I think that some mistake in my macro and I am trying to rectify it. If I continue to have problem I shall revert to this thread. thanks

Re: excel sorting by custom list

thank you very much. that is what I actually did and used a macro to add a custom list and also sort according to custom list. some how the ascending order within custom list takes place.
Then I manually sorted with custom list and then order remains same as custom list and not in ascending order,.
that means that something is wrong in the macro., I shall check the macro and if the problem persists I shall revert to the newsgroup.
thanks again

the problem is more complicated but I am simplifying it so that I can understand

my customlist is
venkat
john
lyndon

sorting should be done just as above and not

venkat
lyndon
john
(descending order)

nor do i want
john
lyndon
venkat
(ascending order)

is it possible?

sorting arranges the custom list either in ascending or descending order

of course I can have a helping column identifying by numbers e.g

venkat 1
john 2
lyndlon 3

But I want to avoid this.

Re: Ranking based on an arbitrary order

why dont you make arrays in fueil2 in each column as different custom lists and sort according to particular list

you can see custom list
in excel 2007
click excel icon on the top left end
click excel options
popular(first item on the left)
click edit custom list(6th item from the top)

once this is added to custom list you can sort according to this custom list

of course you can write a macro for adding to custom list

once custom lilst is used you can delete them from custom list
you can try on that line

Re: (VBA) Compare data from two different workbook and highlight MATCHES

sheets(1) means first sheet in the workbook
sheets("1") means the sheet NAMED 1 in the workbook

if you are not very familiar with macro try to have two sheets in one workbook,make that workbook active and write the macro

later you can save each sheet in two workbooks

it is better to start with easier configuration rather than complex

Re: vba to select multiple sheets based on a condition

this macro is not tested
open a new workbook and call it "new". you can rename later
activate the workbook which has the filtered data

now try this macro (I have not tested)
if you want you can modify the macro

Sub test()
'open a new workbook and name it "new" and keep it open
Dim j As Integer, r As Range, rfilter As Range, dest As Range
'activate the workbook which has original data
With ActiveWorkbook
For j = 1 To .Worksheets.Count
With Worksheets(j)
Set r = .Range("a1").CurrentRegion
Set rfilter = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
rfilter.Copy
With Workbooks("new")
With .Worksheets("sheet1")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest.PasteSpecial
End With
End With
End With
Next j
End With


End Sub

Re: Macro to copy range based on dynamic cell value

how is your data designed, when you overtype in D2 the previous value disappears
so you have to save the historical data somewhere.