How to record data that created based on a drop-down list in Excel using VBA
I have an excel spreadsheet that give me a result column (say 100 rows) based on the selection from a common drop down list (say 30 nos). I want to record the result column for all the values in the drop down list in a separate table (like 30columns x 100 rows).
Can anyone please help me with this?
Thank you in advance
See also questions close to this topic
- need to merge 3 columns in excel
Code Runs Only If Visual Basic Editor is Open
I have a really weird problem about VBA. I tried to list circular references at activeworkbook and i have written below code for that. It only works if i press ALT+F11. So if VBA Editor window is open, code runs correctly but otherwise it is not working. By the way, code is in a module at Addin and i call it from ribbon. You may see the code below. Your help is highly appreciated.
Type SaveRangeCir Val As Variant Addr As String Preaddress As String Shtname As String Workbname As String End Type Public OldCir() As SaveRangeCir Sub DonguselBasvurulariBul(control As IRibbonControl) Dim wba As Workbook Dim ws As Worksheet Dim wsa As Worksheet Dim sht As Worksheet Dim sht2 As Worksheet Dim dummy As Worksheet Dim Item As Range Dim crcell As Range Dim cll As Range un = "Sayin " & Environ("UserName") muyarcirc = MsgBox("Lutfen Oncelikle Dosyanizi Kaydedin" & vbNewLine & vbNewLine & _ "-->> Dosyanizi Kaydettiniz mi?", vbExclamation + vbYesNo, un) If muyarcirc = vbno Then muyar2 = MsgBox("Dongusel Basvuru Arama Islemi Iptal Edildi", vbInformation, un) Exit Sub End If With Application .DisplayAlerts = False .ScreenUpdating = False .EnableEvents = False End With On Error Resume Next Set wba = ActiveWorkbook Set wsa = wba.ActiveSheet Worksheets.Add Set dummy = ActiveSheet For Each sht2 In wba.Sheets If sht2.Name = "Dongusel Basvurular" Then sht2.Delete End If Next sht2 wba.Worksheets.Add Set ws = wba.ActiveSheet dummy.Delete With ws .Name = "Dongusel Basvurular" .Range("A1") = "Dongusel Basvuru Hucresi" .Range("B1") = "Dongusel Basvuru Hucresi Formul Degeri" .Range("C1") = "Bagli Oldugu Alan" .Range("D1") = "Bulundugu Sayfa" .Range("E1") = "Bulundugu Dosya" End With With wba For Each sht In .Worksheets If sht.CodeName <> ws.CodeName Then sht.Activate crcell = Nothing Do Set crcell = sht.CircularReference If Not crcell Is Nothing Then ReDim Preserve OldCir(1 To crcell.Precedents.Cells.Count) i = 0 For Each cll In crcell.Precedents i = i + 1 OldCir(i).Addr = cll.Address OldCir(i).Val = cll.Formula OldCir(i).Preaddress = cll.Precedents.Address OldCir(i).Shtname = cll.Parent.Name OldCir(i).Workbname = cll.Parent.Parent.Name cll.Value = cll.Value Next cll For j = LBound(OldCir) To UBound(OldCir) lr = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ws.Cells(lr, 1) = OldCir(j).Addr ws.Cells(lr, 2) = "'" & OldCir(j).Val ws.Cells(lr, 3) = OldCir(j).Preaddress ws.Cells(lr, 4) = OldCir(j).Shtname ws.Cells(lr, 5) = OldCir(j).Workbname ws.Hyperlinks.Add Anchor:=ws.Cells(lr, 1), Address:="", SubAddress:=ws.Cells(lr, 4) & "!" & ws.Cells(lr, 1), _ ScreenTip:="Dongusel Basvuru Hucresini Gormek icin Tiklayiniz" Next j Else GoTo skipsheet End If Erase OldCir Set crcell = sht.CircularReference Loop While crcell.Cells.Count > 0 lr2 = ws.Cells(Rows.Count, 1).End(xlUp).Row For m = 2 To lr2 If ActiveSheet.Name <> ws.Cells(lr2, "D") Then wba.Sheets(ws.Cells(m, "D")).Activate End If Range(ws.Cells(m, 1)).Formula = "=" & Right(ws.Cells(m, 2), Len(ws.Cells(m, 2)) - 1) Next m End If skipsheet: Next sht If ws.Range("A2") = "" Then ws.Delete wsa.Activate m1 = MsgBox("Aktif Dosyada Dongusel Basvuru Bulunamadi", vbInformation, "Sayin " & Environ("UserName")) Else ws.Activate ws.Range("A1:E1").EntireColumn.AutoFit End If End With Erase OldCir Set crcell = Nothing With Application .DisplayAlerts = True .ScreenUpdating = True .EnableEvents = True End With End Sub
what is the best way for testing visual basic code
we have a GUI in visual basic code, we need to test some apps in it. Can someone please help me in suggesting what is the best way to automate this testing?
Need help to convert excel macro
Recently, I did a table check script on a macro excel. Now, i want to convert this macro in .exe but i can't. So, i need some help to convert my script to vb.net 2010 to do an .exe !
Thank you for taking my request into account.
Wishing you a good day.
Action between massive selection in InputBox
I'm getting string array from InputBox with:
Set productName = Application.InputBox("Select products from list:", _ "Our dialog", , , , , , 8)
I want to color the cells, which I have chosen, after each choice. For example, I want to select 10 cells (with one
InputBoxaction). I choose one (on this step I want to color this cell, to remember, that I have already chosen it), then press CTRL , choose second (color the cell), choose third (color the cell) etc.
Is it possible to make this with VBA?